树节点
题目分析:才开始一看还以为是编程题,结果不是,哈哈
这题主要还是用union,针对不同的类型进行筛选后连接
整个过程如下
#根节点
#根节点
select id Id,"Root" as Type from tree where p_id is null
union
#叶子节点
select id Id,"Leaf" as Type from tree where id not in (select ifnull(p_id,-1) from tree) and p_id is not null
union
#内部节点
select id Id,"Inner" as Type from tree where id in (select ifnull(p_id,-1) from tree) and id not in (select id from tree where p_id is null) order by Id
提交结果如下