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
How about this:
CASE WHEN parent.p_id IS NULL THEN 'Root'
WHEN child.id is NOT NULL THEN 'Inner'
ELSE 'Leaf' END as Type
from tree parent
left join tree child
on child.p_id = parent.id
order by id;
,CASE WHEN P_ID IS NULL THEN 'Root'
WHEN ID IN (SELECT P_ID FROM TREE) THEN 'Inner'
END AS TYPE
Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.