mysql - 查找内部节点、叶节点和根节点的 SQL 查询

Question

我有如下表格:

ID   P_ID
1      2
3      2
6      8
9      8
2      5
8      5
5     null

我用过查询

SELECT ID,  
   CASE 
        WHEN P_ID IS NULL THEN 'Root'
        WHEN NOT EXISTS (SELECT ID FROM MINOA.TREE WHERE P_ID=T0.ID) THEN 'Leaf'
        ELSE 'Inner'
    END T
FROM MINOA.TREE T0
ORDER BY ID;

我得到的输出是:

ID     P_ID
1      leaf
2      inner
3      leaf
5      inner
6      leaf
8      inner
9      leaf

但是对于 ID 5 它应该返回 Root

Answer 1

我创建了这样的表:

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| p_id  | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

你的值(value)观:

+------+------+
| id   | p_id |
+------+------+
|    1 |    2 |
|    3 |    2 |
|    6 |    8 |
|    9 |    8 |
|    2 |    5 |
|    8 |    5 |
|    5 | NULL |
+------+------+

执行此查询:

SELECT id, CASE WHEN p_id IS NULL THEN 'Root' WHEN NOT EXISTS (SELECT id FROM tree WHERE p_id = t0.id) THEN 'Leaf' ELSE 'Inner' END as PROPERTY from tree t0 order by id;

返回:

+------+----------+
| id   | PROPERTY |
+------+----------+
|    1 | Leaf     |
|    2 | Inner    |
|    3 | Leaf     |
|    5 | Root     |
|    6 | Leaf     |
|    8 | Inner    |
|    9 | Leaf     |
+------+----------+