题目 ：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