Click here to see the full article post
it seems need to add "order by atree.id " for last solution.
@xinyulrsm Thanks for the feedback. It has been added.
so like you cant see the question but can see the solution in locked questions? interesting :)
when p_id is null then 'Root'
when id in (select t.p_id from tree as t) then 'Inner'
end as type
order by id;
my CASE solution simpler than this solution
A left outer join would be much simpler and more straightforward.
distinct t1.id, (case
when t1.p_id is null then 'Root'
when t2.id is null then 'Leaf'
else 'Inner' end
) as type
from tree t1
left join tree t2 on t1.id = t2.p_id
order by 1
SELECT id, 'Root' AS type FROM tree WHERE p_id is NULL UNION SELECT id, 'Inner' AS type FROM tree WHERE p_id IS NOT NULL and id IN (SELECT p_id FROM tree WHERE p_id IS NOT NULL) UNION SELECT id, 'Leaf' AS type FROM tree WHERE p_id IS NOT NULL and id NOT IN (SELECT p_id FROM tree WHERE p_id IS NOT NULL) ORDER BY id ;
Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.