2308. 按性别排列表格-CSDN博客

本文链接：https://blog.csdn.net/CYJ1844/article/details/143806666

力扣题目跳转（. - 力扣（LeetCode））

表: Genders

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| gender      | varchar |
+-------------+---------+
user_id 是该表的主键(具有唯一值的列)。
gender 的值是 'female', 'male','other' 之一。
该表中的每一行都包含用户的 ID 及其性别。
表格中 'female', 'male','other' 数量相等。

题目要求：

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

示例 1:

输入: 
Genders 表:
+---------+--------+
| user_id | gender |
+---------+--------+
| 4       | male   |
| 7       | female |
| 2       | other  |
| 5       | male   |
| 3       | female |
| 8       | male   |
| 6       | other  |
| 1       | other  |
| 9       | female |
+---------+--------+
输出: 
+---------+--------+
| user_id | gender |
+---------+--------+
| 3       | female |
| 1       | other  |
| 4       | male   |
| 7       | female |
| 2       | other  |
| 5       | male   |
| 9       | female |
| 6       | other  |
| 8       | male   |
+---------+--------+
解释: 
女性：ID 3、7、9。
其他性别：ID 1、2、6。
男性：ID 4、5、8。
我们在 'female', 'other','male' 之间交替排列表。
注意，每种性别都是按 user_id 升序排序的。

case 1 的建表语句。

Create table If Not Exists Genders (user_id int, gender ENUM('female', 'other', 'male'))
Truncate table Genders
insert into Genders (user_id, gender) values ('4', 'male')
insert into Genders (user_id, gender) values ('7', 'female')
insert into Genders (user_id, gender) values ('2', 'other')
insert into Genders (user_id, gender) values ('5', 'male')
insert into Genders (user_id, gender) values ('3', 'female')
insert into Genders (user_id, gender) values ('8', 'male')
insert into Genders (user_id, gender) values ('6', 'other')
insert into Genders (user_id, gender) values ('1', 'other')
insert into Genders (user_id, gender) values ('9', 'female')

一我们先按照性别分组，user id 进行排名。

select * ,
        row_number() over (partition by gender order by user_id) as rn
from genders;

输出如下

select * ,
        row_number() over (partition by gender order by user_id) * 3 - 3 as rn
from genders;

二我们再对 rn 进行调整即可。

with tmp as
(select * ,
        row_number() over (partition by gender order by user_id) * 3 - 3 as rn
from genders)
select * ,
        case
            when gender = 'female' then rn
            when gender = 'other' then rn + 1
            when gender = 'male' then rn + 2
        end as cnt
from tmp;

输出如下

with tmp as
(select * ,
        row_number() over (partition by gender order by user_id) * 3 - 3 as rn
from genders),tmp1 as
(select * ,
        case
            when gender = 'female' then rn
            when gender = 'other' then rn + 1
            when gender = 'male' then rn + 2
        end as cnt
from tmp)
select user_id,gender from tmp1 order by cnt;