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
    

  • 0
    N

    How about this:
    select
    distinct
    parent.id,
    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;


  • 0
    L

    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;


  • 0
    C

    Thanks for sharing your solution. I found the LEFT JOIN and IF combination is easier for me to understand:

    SELECT DISTINCT a.id AS id, IF(a.p_id IS NULL, 'Root', IF(b.id IS NULL, 'Leaf', 'Inner')) AS Type
    FROM tree a LEFT JOIN tree b
    ON a.id = b.p_id


Log in to reply
 

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