Tree Node


  • 0

    Click here to see the full article post


  • 0
    X

    it seems need to add "order by atree.id " for last solution.


  • 0

    @xinyulrsm Thanks for the feedback. It has been added.


  • 0
    U

    so like you cant see the question but can see the solution in locked questions? interesting :)


  • 0
    X

    Couldn't get your point.


  • 0
    C

    '''select id,case
    when p_id is null then 'Root'
    when id in (select t.p_id from tree as t) then 'Inner'
    else 'Leaf'
    end as type
    from tree
    order by id;
    '''
    my CASE solution simpler than this solution


  • 0
    L

    A left outer join would be much simpler and more straightforward.
    select
    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
    ;


  • 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
    ;
    

  • 1

    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
    

Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.