mysql 在SQL中查找树节点

hmtdttj4 于 2023-01-16 发布在 Mysql

关注(0)|答案(2)|浏览(130)

大家好，最近有一个SQL问题在优步被问到，这真的很有趣，也有点难。

问题如下

表格：树 *

+-------------+------+
| 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.][1]][1]

示例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.

现在我已经给出了一个答案，但我想用其他一些方法得到相同的结果，如果有，请帮助我用其他一些方法处理这类问题

mysql

来源：https://stackoverflow.com/questions/71913186/find-tree-node-in-sql

2条答案

按热度按时间

hc8w905p1#

您也可以使用连接

select distinct a.id,
   case when a.p_id is null then 'root'
        when b.id is null then 'leaf'
        else 'inner' end node_type
from tree a
left join tree b on a.id = b.p_id
order by a.id

赞(0）回复(0）举报 2023-01-16

sgtfey8w2#

SELECT id, p_id 
, case when p_id is null then "root"
     when p_id is not null and id in (select distinct p_id from tree) then "Inner"
     else "Leaf"
end as type
from tree

赞(0）回复(0）举报 2023-01-16

我来回答

mysql 在SQL中查找树节点

2条答案

相关问题

热门标签

最新问答