【MySQL】union (all) 后 order by 子查询排序不生效问题解决方案

力扣题

1、题目地址

2308. 按性别排列表格

2、模拟表

表：Genders

Column Name	Type
user_id	int
gender	varchar

• user_id 是该表的主键(具有唯一值的列)。
• gender 的值是 ‘female’，‘male’，‘other’ 之一。
• 该表中的每一行都包含用户的 ID 及其性别。
• 表格中 ‘female’，‘male’，‘other’ 数量相等。

3、要求

编写一个解决方案以重新排列 Genders 表，使行按顺序在 ‘female’，‘other’ 和 ‘male’ 之间交替。同时每种性别按照 user_id 升序进行排序。
按 上述顺序 返回结果表。
返回结果格式如以下示例所示。

示例 1：

输入：
Genders 表：

user_id	gender
15	other
12	female
1	other
2	female
21	other
5	male
20	male
14	other
11	male
4	male
19	male
18	other
6	other
9	female
3	female
8	female
10	male
7	other
16	male
13	female
17	female

输出：

user_id	gender
2	female
1	other
4	male
3	female
6	other
5	male
8	female
7	other
10	male
9	female
14	other
11	male
12	female
15	other
16	male
13	female
18	other
19	male
17	female
21	other
20	male

解释：
女性：ID 2、3、8、9、12、13、17。
其他性别：ID 1、6、7、14、15、18、21。
男性：ID 4、5、10、11、16、19、20。
我们在 ‘female’，‘other’,‘male’ 之间交替排列表。
注意，每种性别都是按 user_id 升序排序的。

4、代码编写

错误写法（union (all) 后 order by 子查询排序不生效）

SELECT user_id, gender
FROM (
    SELECT row_number() over (order by user_id) AS id, user_id, gender
    FROM Genders
    WHERE gender = 'female'
    UNION ALL
    SELECT row_number() over (order by user_id) AS id, user_id, gender
    FROM Genders
    WHERE gender = 'other'
    UNION ALL
    SELECT row_number() over (order by user_id) AS id, user_id, gender
    FROM Genders
    WHERE gender = 'male'
) AS one
ORDER BY id

| user_id | gender |
| ------- | ------ |
| 4       | male   |
| 1       | other  |
| 2       | female |
| 3       | female |
| 5       | male   |
| 6       | other  |
| 8       | female |
| 10      | male   |
| 7       | other  |
| 14      | other  |
| 11      | male   |
| 9       | female |
| 15      | other  |
| 12      | female |
| 16      | male   |
| 18      | other  |
| 13      | female |
| 19      | male   |
| 21      | other  |
| 17      | female |
| 20      | male   |

正确写法（在 union (all) 前后都加一个字段来保证顺序，后面在 order by 加上就行）

SELECT user_id, gender
FROM (
    SELECT row_number() over (order by user_id) AS id, user_id, gender, 0 AS sort
    FROM Genders
    WHERE gender = 'female'
    UNION ALL
    SELECT row_number() over (order by user_id) AS id, user_id, gender, 1 AS sort
    FROM Genders
    WHERE gender = 'other'
    UNION ALL
    SELECT row_number() over (order by user_id) AS id, user_id, gender, 2 AS sort
    FROM Genders
    WHERE gender = 'male'
) AS one
ORDER BY id, sort

| user_id | gender |
| ------- | ------ |
| 2       | female |
| 1       | other  |
| 4       | male   |
| 3       | female |
| 6       | other  |
| 5       | male   |
| 8       | female |
| 7       | other  |
| 10      | male   |
| 9       | female |
| 14      | other  |
| 11      | male   |
| 12      | female |
| 15      | other  |
| 16      | male   |
| 13      | female |
| 18      | other  |
| 19      | male   |
| 17      | female |
| 21      | other  |
| 20      | male   |

网友写法（用的很巧妙）

SELECT * 
FROM Genders
ORDER BY row_number() over(partition by gender order by user_id),
         case when gender = 'female' then 1 
              when gender = 'male' then 3 
              else 2 end

思考得出的新写法

直接使用 order by gender 是不行的，female-male-other 的顺序不符合要求 female-other-male，刚好可以使用长度倒序去做

SELECT *
FROM genders
ORDER BY row_number() over(partition by gender order by user_id),
         length(gender) desc

参考

mysql union (all) 后 order by 的排序失效问题解决