[leetcode] 608. Tree Node

608. Tree Node

https://leetcode.com/problems/tree-node/

Tree Node - LeetCode

 

Each node in the tree can be one of three types:

• "Leaf": if the node is a leaf node.
• "Root": if the node is the root of the tree.
• "Inner": If the node is neither a leaf node nor a root node.

Write an SQL query to report the type of each node in the tree.

# 트리의 각 노드 유형을 보고하는 SQL 쿼리 작성

 

 

예시

Input: 
Tree table:
+----+------+
| id | p_id |
+----+------+
| 1  | null |
| 2  | 1    |
| 3  | 1    |
| 4  | 2    |
| 5  | 2    |
+----+------+
Output: 
+----+-------+
| id | type  |
+----+-------+
| 1  | Root  |
| 2  | Inner |
| 3  | Leaf  |
| 4  | Leaf  |
| 5  | Leaf  |
+----+-------+

 

답

SELECT A.id
    ,(CASE WHEN A.p_id IS NULL THEN 'Root' -- 부모 노드가 없으면 root
        WHEN EXISTS(SELECT * FROM TREE T WHERE T.p_id = A.id) THEN 'Inner'
        ELSE 'Leaf'
        END) AS type
FROM Tree A;

 

EXISTS

서브쿼리에 데이터가 존재하는지 체크
서브쿼리에만 사용할 수 있다.

예시

SELECT *
 FROM table1 t1
 WHERE t1.col IN (20, 30, 40)
   AND EXISTS (SELECT 1 -- 서브쿼리에 사용
                 FROM table2 t2
                WHERE t2.col between 500 and 1300
                  AND t2.col2 = t1.col2)

서브쿼리에 1건이라도 존재하면 TRUE를 반환