题目 :https://leetcode.cn/problems/tree-node/
数据
Create table If Not Exists Tree (id int, p_id int)
insert into Tree (id, p_id) values ('1', 'None')
insert into Tree (id, p_id) values ('2', '1')
insert into Tree (id, p_id) values ('3', '1')
insert into Tree (id, p_id) values ('4', '2')
insert into Tree (id, p_id) values ('5', '2')
需求
查询所有节点的编号和节点的类型,并将结果按照节点编号排序
- 叶子:如果这个节点没有任何孩子节点
- 根:如果这个节点是整棵树的根,即没有父节点
- 内部节点:如果这个节点既不是叶子节点也不是根节点
查询结果 :
| id | type |
| 1 | Root |
| 2 | Inner|
| 3 | Leaf |
| 4 | Leaf |
| 5 | Leaf |
解决
-- 根 : p_id 为空
select id,
'Root' as type
from tree
where p_id is null
union all
-- 内部节点 : 父节点有该节点
select t1.id,
'Inner' as type
from tree t1 join tree t2
on t1.id = t2.p_id
where t1.p_id is not null
group by t1.id
union all
-- 叶子 : 父节点没有该节点
select t3.id,
'Leaf' as type
from tree t3 left join tree t4
on t3.id = t4.p_id
where t4.id is null
and t3.p_id is not null
group by t3.id