# 608.(Medium)树节点

给定一个表 tree，id 是树节点的编号， p\_id 是它父节点的 id 。

```
+----+------+
| id | p_id |
+----+------+
| 1  | null |
| 2  | 1    |
| 3  | 1    |
| 4  | 2    |
| 5  | 2    |
+----+------+
树中每个节点属于以下三种类型之一：

叶子：如果这个节点没有任何孩子节点。
根：如果这个节点是整棵树的根，即没有父节点。
内部节点：如果这个节点既不是叶子节点也不是根节点。
```

写一个查询语句，输出所有节点的编号和节点的类型，并将结果按照节点编号排序。上面样例的结果为：

```
+----+------+
| id | Type |
+----+------+
| 1  | Root |
| 2  | Inner|
| 3  | Leaf |
| 4  | Leaf |
| 5  | Leaf |
+----+------+
```

解释

节点 '1' 是根节点，因为它的父节点是 NULL ，同时它有孩子节点 '2' 和 '3' 。 节点 '2' 是内部节点，因为它有父节点 '1' ，也有孩子节点 '4' 和 '5' 。 节点 '3', '4' 和 '5' 都是叶子节点，因为它们都有父节点同时没有孩子节点。 样例中树的形态如下：

```
              1
            /   \
           2     3
         /   \
        4     5
```

注意

如果树中只有一个节点，你只需要输出它的根属性。

来源：力扣（LeetCode）

链接：<https://leetcode-cn.com/problems/tree-node>

著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

## Solution

自连接

```sql
select a.id, (case 
    when count(a.p_id)=0 then 'Root'
    when count(b.id)=0 then 'Leaf'
    else 'Inner' end) Type
from tree a left join tree b on a.id=b.p_id
group by 1
order by 1
```

子查询

```sql
select id,
    case 
    when p_id is null then 'Root'
    when id in (select p_id from tree) then 'Inner'
    else 'Leaf' end Type
from tree
order by 1
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://zqt0.gitbook.io/leetcode/sql/608.tree-node.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.