df-drills pandas · Polars · DuckDB SQL · DuckDB Relational 题目列表 登录

多列不同方向排序

简单 03-sort 排序 sortmulti-keyNone

把员工按以下规则排序:

  1. dept_id 升序;
  2. 同一部门内 salary 降序;
  3. 工资也相同时 emp_id 升序。

  4. null 一律排在最后:dept_id 为 null 的员工排在所有部门之后;同一部门内 salary 为 null 的员工排在该部门最后(第二个 case 里有这种情况)。

输入

  • employees(emp_id, name, dept_id, manager_id, hire_date, salary, title, email)

输出

列为 emp_id, name, dept_id, salary,行顺序必须与上面的排序规则一致。

输入表 (case base,共 2 个 case:base、nulls)

employees hr/employees
#emp_idint64namestringdept_idint64manager_idint64hire_datedatesalaryint64titlestringemailstring
1 1 Grace Liu 1 null 2015-10-01 98000 CEO grace.liu@corp.example
2 2 Leo Wang 2 1 2017-09-09 72000 VP Engineering leo.wang@corp.example
3 3 Sara Chen 3 1 2020-06-05 70000 VP Sales sara.chen@corp.example
4 4 Lucas Zhang 4 2 2023-04-20 52000 Engineering Manager lucas.zhang@corp.example
5 5 Mia Zhao 5 2 2016-07-16 52000 Data Manager mia.zhao@corp.example
6 6 Kevin Wu 3 3 2016-10-23 45000 Sales Manager kevin.wu@corp.example
7 7 Lily Xu 6 4 2025-03-17 48000 Infra Lead lily.xu@corp.example
8 8 Tom Huang 4 4 2022-01-03 46000 Senior Engineer tom.huang@corp.example
9 9 Liam Zhou 4 4 2020-10-01 39000 Software Engineer liam.zhou@corp.example
10 10 Nina Yang 4 4 2020-10-02 39000 Software Engineer nina.yang@corp.example
11 11 Oscar Lin 4 8 2022-07-15 26000 Junior Engineer oscar.lin@corp.example
12 12 Linda Ma 5 5 2016-08-25 41000 Data Engineer linda.ma@corp.example
13 13 Eric Sun 5 5 2021-09-09 33000 Data Analyst eric.sun@corp.example
14 14 Ivy He 5 5 2018-05-29 41000 Data Engineer ivy.he@corp.example
15 15 Lei Gao 5 12 2016-06-02 27000 Junior Data Engineer lei.gao@corp.example
16 16 Hao Guo 6 7 2016-12-18 44000 Site Reliability Engineer hao.guo@corp.example
17 17 Fang Luo 6 7 2020-11-11 38000 Infra engineer fang.luo@corp.example
18 18 Jun Liang 6 16 2020-09-08 25000 Junior Engineer jun.liang@corp.example
19 19 Dana Song 3 6 2016-10-13 31000 Account Executive dana.song@corp.example
20 20 Victor Tang 3 6 2018-09-12 31000 Account Executive victor.tang@corp.example
显示前 20 行,共 40 行

期望输出 (case base,行顺序要一致)

#emp_idInt64nameStringdept_idInt64salaryInt64
1 1 Grace Liu 1 98000
2 2 Leo Wang 2 72000
3 33 Larry Ren 2 66000
4 3 Sara Chen 3 70000
5 32 Amy Yu 3 46500
6 6 Kevin Wu 3 45000
7 22 Lu Feng 3 36000
8 19 Dana Song 3 31000
9 20 Victor Tang 3 31000
10 21 Wen Han 3 29500
11 36 王磊 3 28000
12 40 Sophie Fan 3 28000
13 23 Yan Deng 3 12000
14 30 Bob Pan 4 53000
15 4 Lucas Zhang 4 52000
16 8 Tom Huang 4 46000
17 39 Logan Bai 4 46000
18 9 Liam Zhou 4 39000
19 10 Nina Yang 4 39000
20 25 Ping Peng 4 37000
21 35 Zhang Min 4 37000
22 26 Qing Xie 4 30000
23 11 Oscar Lin 4 26000
24 5 Mia Zhao 5 52000
25 29 Xin Cai 5 47000
26 12 Linda Ma 5 41000
27 14 Ivy He 5 41000
28 13 Eric Sun 5 33000
29 15 Lei Gao 5 27000
30 34 Wei Shi 5 26000
31 37 李静 5 23000
32 7 Lily Xu 6 48000
33 16 Hao Guo 6 44000
34 17 Fang Luo 6 38000
35 31 Chen Du 6 38000
36 18 Jun Liang 6 25000
37 38 Henry Kong 6 25000
38 24 Zoe Cao 7 61000
39 28 Uma Jiang null 24000
40 27 Rui Lu null 22000
共 40 行