SQL：树节点

题目描述：给定一个表 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

---

解析：

1.根节点查找:

根节点是没有父节点的节点

SELECT
    id, 'Root' AS Type
FROM
    tree
WHERE
    p_id IS NULL;

结果：

id	Type
1	Root

2. 叶子节点查找

叶子节点是没有孩子节点的节点（也就是p_id没有他的存在），但是存在父节点

SELECT
    id, 'Leaf' AS Type
FROM
    tree
WHERE
    id NOT IN (SELECT DISTINCT
            p_id
        FROM
            tree
        WHERE
            p_id IS NOT NULL)
        AND p_id IS NOT NULL

结果：

id	Type
3	LEAF
4	LEAF
5	LEAF

3. 内部节点

内部节点有父节点（指的是自己的p_id 不为NULL），也有子节点（指的是整个表中的p_id 的数据集合中也存在自己的ID）

SELECT
		id,'Inner' AS Type
FROM
		tree
WHERE
		id  IN (SELECT DISTINCT
						p_id
					FROM
						tree
					WHERE
					tree.p_id IS NOT NULL)
					AND p_id IS NOT NULL;

结果:

id	Type
2	Inner

方法一：采用UNION

将上述分析中的 采用 UNION 结合

SELECT
    id, 'Root' AS Type
FROM
    tree
WHERE
    p_id IS NULL

UNION

SELECT
    id, 'Leaf' AS Type
FROM
    tree
WHERE
    id NOT IN (SELECT DISTINCT
            p_id
        FROM
            tree
        WHERE
            p_id IS NOT NULL)
        AND p_id IS NOT NULL

UNION

SELECT
    id, 'Inner' AS Type
FROM
    tree
WHERE
    id IN (SELECT DISTINCT
            p_id
        FROM
            tree
        WHERE
            p_id IS NOT NULL)
        AND p_id IS NOT NULL
ORDER BY id;

结果:

id	Type
1	Root
2	Inner
3	Leaf
4	Leaf
5	Lea

 方法二 使用 case when

SELECT
	id AS 'Id',
CASE
		
		WHEN tree.id = ( SELECT atree.id FROM tree atree WHERE atree.p_id IS NULL) THEN  'Root' 
		WHEN tree.id IN ( SELECT atree.p_id FROM tree atree ) THEN
		'Inner' ELSE 'Leaf' 
	END AS Type 
FROM
	tree 
ORDER BY
	'Id';

结果：

id	Type
1	Root
2	Inner
3	Leaf
4	Leaf
5	Leaf

 方法三、使用 IF语句判断

SELECT
	atree.id,
	IF(ISNULL(atree.p_id),
	'Root',
	IF(atree.id IN(SELECT p_id FROM tree),'Inner','Leaf')) Type
	FROM
	tree atree
	ORDER BY atree.id;

结果:

id	Type
1	Root
2	Inner
3	Leaf
4	Leaf
5	Leaf