We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Prepare
  2. SQL
  3. Advanced Select
  4. Binary Tree Nodes
  5. Discussions

Binary Tree Nodes

  • j_tianhuilu
     + 1 comment

    I found 3 solutions:

    Method 1

    SELECT N, 
    CASE WHEN P is NULL Then 'Root' 
    WHEN N NOT IN 
        (SELECT B1.P FROM BST B1 
         JOIN BST B2 
         ON B1.P=B2.N ) Then 'Leaf' 
    ELSE 'Inner' END AS Node 
    FROM BST 
    ORDER BY N

    Method 2:

    SELECT N,
    CASE WHEN P IS NULL THEN 'Root'
    WHEN N IN
        (SELECT P FROM BST WHERE P IS NOT NULL)
    THEN 'Inner'
    ELSE 'Leaf' END AS Node
FROM BST
ORDER BY N;

    Method 3:

    SELECT N,
    CASE WHEN P IS NULL THEN 'Root'
    WHEN N NOT IN
        (SELECT P FROM BST WHERE P IS NOT NULL)
    THEN 'Leaf'
    ELSE 'Inner' END AS Node
FROM BST
ORDER BY N;

    The key is to specify 'P IS NOT NULL' so as to separate the Leaf and Inner nodes.