问题描述：
有这样一张维度表(id name)，存放部门的id，与部门负责人
000 Liam
000001 Noah
000001001 Oliver
000002 Emma
001 Elijah
001001 James
001002 Charlotte
001001001 William
001002001 Amelia
002 Benjamin
002001 Lucas
002001001 Theodore
需要合并相同部门的人，也就是相同前缀的id的多行记录，合并到一行记录上
期望结果(为空表示该条记录只有对应的二级部门，没有三级部门)
000 Liam 000002 Emma NULL NULL
000 Liam 000001 Noah 000001001 Oliver
001 Elijah 001001 James 001001001 William
001 Elijah 001002 Charlotte 001002001 Amelia
002 Benjamin 002001 Lucas 002001001 Theodore
实际案例：
建表
create table test.dept(id string, name string) row format delimited fields terminated by '\t';
load data inpath '/user/admin/dept.txt' into table dept;
select * from test.dept;
分析
因为源表是将同一部门id的数据存在了不多行，所以首先我们需要找出能关联的键，也就是下一级的前3位部门id等于上一级的部门id
子查询先筛选出各级部门id的数据，与上一级通过前三位关联
SQL
SELECT t1.id AS id_level1,
t1.name AS name_level1,
t2.id id_level12,
t2.name AS name_level2,
t3.id id_level3,
t3.name AS name_level3
FROM
(SELECT *
FROM test.dept
WHERE length(id)=3)t1
LEFT JOIN
(SELECT *
FROM test.dept
WHERE length(id)=6)t2 ON t1.id=substr(t2.id,0,3)
LEFT JOIN
(SELECT *
FROM test.dept
WHERE length(id)=9)t3 ON t2.id=substr(t3.id,0,6)
ORDER BY id_level3;
结果符合预期
id_level1 name_level1 id_level12 name_level2 id_level3 name_level3
000 Liam 000002 Emma NULL NULL
000 Liam 000001 Noah 000001001 Oliver
001 Elijah 001001 James 001001001 William
001 Elijah 001002 Charlotte 001002001 Amelia
002 Benjamin 002001 Lucas 002001001 Theodore
推荐文章
您阅读本篇文章共花了：
发表评论