【LeetCode2308. 按性别排列表格】MySQL通过field()函数实现自定义排序
题目地址
https://leetcode.cn/problems/arrange-table-by-gender/description/
题目描述
编写一个解决方案以重新排列 Genders 表，使行按顺序在 'female', 'other' 和 'male' 之间交替。同时每种性别按照 user_id 升序进行排序。
按 上述顺序 返回结果表。
返回结果格式如以下示例所示。
代码
MySQL，使用field函数
with t1 as(
select *,
row_number() over(partition by gender order by user_id asc) as rn
from Genders
)
select user_id,gender from t1
order by rn asc,
field(gender,"female","other","male") asc
Oracle/PostgreSQL/T-SQL/MySQL，使用case when
WITH t1 AS (
SELECT g.*,
ROW_NUMBER() OVER (PARTITION BY gender ORDER BY user_id ASC) AS rn
FROM Genders g
)
SELECT user_id, gender
FROM t1
ORDER BY rn ASC,
CASE gender
WHEN 'female' THEN 1
WHEN 'other' THEN 2
WHEN 'male' THEN 3
ELSE 4
END ASC
Pandas
import pandas as pd
def arrange_table(genders: pd.DataFrame) -> pd.DataFrame:
# Use sort_values and groupby to simulate the ROW_NUMBER()
genders['rn'] = genders.sort_values('user_id').groupby('gender').cumcount() + 1
# Create a mapping for the genders to use in sorting
gender_order = {'female': 1, 'other': 2, 'male': 3}
# Use the mapping to create a sort key, then sort by 'rn' and the gender key
genders['gender_sort_key'] = genders['gender'].map(gender_order).fillna(4)
sorted_genders = genders.sort_values(by=['rn', 'gender_sort_key'])
# Drop the helper columns as they are no longer needed
sorted_genders = sorted_genders.drop(columns=['rn', 'gender_sort_key'])
return sorted_genders
