Notice
Recent Posts
Recent Comments
Link
일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | ||||
4 | 5 | 6 | 7 | 8 | 9 | 10 |
11 | 12 | 13 | 14 | 15 | 16 | 17 |
18 | 19 | 20 | 21 | 22 | 23 | 24 |
25 | 26 | 27 | 28 | 29 | 30 | 31 |
Tags
- row_number
- LeetCode
- Hard
- 패캠챌린지
- Hackerrank
- 파이썬을활용한시계열데이터분석AtoZ올인원패키지Online
- recursive
- 파이썬을활용한시계열데이터분석
- solvesql
- MySQL
- lv.4
- 패스트캠퍼스
- HACKER_RANK
- 다시풀어보기
- meidum
- 시계열데이터분석
- SELF-JOIN
- SQL
- 직장인자기계발
- medium
- 직장인인강
- 어려웠음
- RANK
- 프리미엄
- group by
- join
- 패스트캠퍼스후기
- 프로그래머스
- easy
- 해커랭크
Archives
- Today
- Total
~고군분투 인생살이~
[608_MEDIUM] Tree Node 다시풀기 본문
Table: Tree
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| p_id | int |
+-------------+------+
id is the primary key column for this table.
Each row of this table contains information about the id of a node and the id of its parent node in a tree.
The given structure is always a valid tree.
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.
Return the result table ordered by id in ascending order.
The query result format is in the following example.
Example 1:
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 |
+----+-------+
Explanation:
Node 1 is the root node because its parent node is null and it has child nodes 2 and 3.
Node 2 is an inner node because it has parent node 1 and child node 4 and 5.
Nodes 3, 4, and 5 are leaf nodes because they have parent nodes and they do not have child nodes.
Example 2:
Input:
Tree table:
+----+------+
| id | p_id |
+----+------+
| 1 | null |
+----+------+
Output:
+----+-------+
| id | type |
+----+-------+
| 1 | Root |
+----+-------+
Explanation: If there is only one node on the tree, you only need to output its root attributes.
쿼리
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
'SQL > LEETCODE' 카테고리의 다른 글
[176_MEDIUM] Second Highest Salary_RE (0) | 2022.10.02 |
---|---|
[511_EASY] Game Play Analysis I (1) | 2022.09.30 |
[184_MEDIUM] Department Highest Salary 다시풀기 (0) | 2022.09.26 |
[180_MEDIUM] Consecutive Numbers 다시풀기 (0) | 2022.09.26 |
[197_EASY] Rising Temperature (0) | 2022.09.25 |
Comments