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

自连接与 set_alias

中等 25-duckdb-rel-only DuckDB Relational 专属 self-joinset-aliasleft-joinhierarchy

列出每个员工和他的直属经理的名字。

  • 经理由 manager_id 指向同一张表里的 emp_id。
  • 每个员工都要输出:没有经理(manager_id 为 null),或者 manager_id 在表里找不到对应员工时,manager_name 为 null。
  • 员工可能重名,要按 id 匹配,不能按名字。

输入

  • employees(emp_id, name, dept_id, manager_id, hire_date, salary, title, email)(已绑定为 relation)

输出

列为 emp_id, emp_name, manager_name,每个员工一行,行顺序不限。

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

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_idInt64emp_nameStringmanager_nameString
1 2 Leo Wang Grace Liu
2 3 Sara Chen Grace Liu
3 4 Lucas Zhang Leo Wang
4 5 Mia Zhao Leo Wang
5 6 Kevin Wu Sara Chen
6 7 Lily Xu Lucas Zhang
7 8 Tom Huang Lucas Zhang
8 9 Liam Zhou Lucas Zhang
9 10 Nina Yang Lucas Zhang
10 11 Oscar Lin Tom Huang
11 12 Linda Ma Mia Zhao
12 13 Eric Sun Mia Zhao
13 14 Ivy He Mia Zhao
14 15 Lei Gao Linda Ma
15 16 Hao Guo Lily Xu
16 17 Fang Luo Lily Xu
17 18 Jun Liang Hao Guo
18 19 Dana Song Kevin Wu
19 20 Victor Tang Kevin Wu
20 21 Wen Han Kevin Wu
21 22 Lu Feng Kevin Wu
22 23 Yan Deng Wen Han
23 24 Zoe Cao Grace Liu
24 25 Ping Peng Tom Huang
25 26 Qing Xie Tom Huang
26 27 Rui Lu Sara Chen
27 28 Uma Jiang Leo Wang
28 29 Xin Cai Mia Zhao
29 30 Bob Pan Lucas Zhang
30 31 Chen Du Lily Xu
31 32 Amy Yu Kevin Wu
32 33 Larry Ren Leo Wang
33 34 Wei Shi Linda Ma
34 35 Zhang Min Tom Huang
35 36 王磊 Kevin Wu
36 37 李静 Xin Cai
37 38 Henry Kong Hao Guo
38 39 Logan Bai Bob Pan
39 40 Sophie Fan Amy Yu
40 1 Grace Liu null
共 40 行