SQL每日一题(20220530)
with recursive cte as
(select id,pid from T1120
union all
select a.id,a.pid from T1120 a join cte b on a.id=b.pId)
select id,pid,case when pid is null then 'Root'
when count(id)>1 then 'Inner'
when count(id)=1 then 'Leaf'
end TYPE
from cte group by id,pid
select id,pid,
case
when pid is null then 'Root'
when id in (select pid from t1120 where pid is not null group by pid) then 'Inner'
else 'Leaf' end "type"
from t1120
select id,
pid,
case
when pid is null then 'root'
when id in (select pid from t1120 group by pid) then 'inner'
when not exists(select 1 from t1120 b where id=b.pid) then 'leaf'
end as Type
from t1120
SELECT ID,CASE WHEN pid IS NULL THEN 'ROOT' WHEN CONNECT_BY_ISLEAF = 0 THEN 'INNER' ELSE 'LEAF' END AS TYPE
FROM t1120
START WITH ID = 1
CONNECT BY PRIOR ID = pid