力扣題

1、題目地址

2308. 按性別排列表格

2、模擬表

表：Genders

• user_id 是該表的主鍵(具有唯一值的列)。
• gender 的值是 ‘female’，‘male’，‘other’ 之一。
• 該表中的每一行都包含用戶的 ID 及其性別。
• 表格中 ‘female’，‘male’，‘other’ 數(shù)量相等。

3、要求

• 編寫一個(gè)解決方案以重新排列 Genders 表，使行按順序在 ‘female’，‘other’ 和 ‘male’ 之間交替。
• 同時(shí)每種性別按照 user_id 升序進(jìn)行排序。
• 按 上述順序 返回結(jié)果表。

4、示例

輸入：

Genders 表：

輸出：

解釋：

女性：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 升序排序的。

5、代碼編寫

錯(cuò)誤寫法（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) 前后都加一個(gè)字段來保證順序，后面在 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   |

網(wǎng)友寫法（用的很巧妙）

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 的排序失效問題解決文章來源地址http://www.zghlxwxcb.cn/news/detail-797645.html

到了這里，關(guān)于【MySQL】union (all) 后 order by 子查詢排序不生效問題解決方案的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！