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 ;
This one also gets accepted.
SELECT id, CASE WHEN p_id IS NULL THEN 'Root' WHEN id IN (SELECT p_id FROM tree) THEN 'Inner' ELSE 'Leaf' END AS Type FROM tree ORDER BY id
Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.