SQL笔记（一） - LC608 / CASE / IF

发表于 | 阅读次数：

Question

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

1
2
3
4
5
6
7
8
9
+----+------+
| id | p_id |
+----+------+
| 1  | null |
| 2  | 1    |
| 3  | 1    |
| 4  | 2    |
| 5  | 2    |
+----+------+
树中每个节点属于以下三种类型之一： - 叶子：如果这个节点没有任何孩子节点。 - 根：如果这个节点是整棵树的根，即没有父节点。 - 内部节点：如果这个节点既不是叶子节点也不是根节点。

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

Solution

一开始想的是一个比较直接的逻辑，即JOIN出一个包括每个节点的父节点和子节点的大表，再直接判断，类似于： 

1
2
3
4
5
6
7
8
9
+----+------+------+
| id | p_id | c_id |
+----+------+------+
| 1  | null | 2    |
| 2  | 1    | 4    |
| 3  | 1    | null |
| 4  | 2    | null |
| 5  | 2    | null |
+----+------+------+
很快发现这样很难写出来，主要是子节点的处理很麻烦。但事实上我们不需要真正去求子节点，p_id 中出现的 id 即为非叶节点，反之为叶节点，非叶节点的子节点并不重要。于是把原表和非叶节点做连接，根据 t2.p_id 是否为 NULL 判断叶节点： 
1
2
3
4
5
6
7
8
9
10
11
SELECT DISTINCT t1.id, (
    CASE WHEN t1.p_id IS NULL THEN "Root"
         WHEN t2.p_id IS NULL THEN "Leaf"
    ELSE "Inner" END) AS type
FROM tree AS t1
LEFT JOIN (
    SELECT DISTINCT p_id
    FROM tree
    WHERE p_id IS NOT NULL
) AS t2
ON t1.id = t2.p_id
因为受之前的思路影响，这里还是想着用 JOIN 去做，事实上并不需要，可以进一步简化： 
1
2
3
4
5
SELECT id, (
    CASE WHEN p_id IS NULL THEN "Root"
         WHEN id IN (SELECT t.p_id FROM tree AS t) THEN "Inner"
    ELSE "Leaf" END) AS type
FROM tree

使用 IF 函数也是一个很便捷的思路，但这个语法不熟，没有想到： 

1
2
3
4
5
6
7
SELECT
    atree.id,
    IF(ISNULL(atree.p_id),
        'Root',
        IF(atree.id IN (SELECT p_id FROM tree), 'Inner','Leaf')) Type
FROM
    tree atree

Keypoint

CASE 语句

1
2
3
4
5
6
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

CASE 语句遍历条件并在满足第一个条件时返回一个值。因此，一旦条件为真，它将停止读取并返回结果。如果没有条件为真，则返回 ELSE 子句中的值，如果没有 ELSE 子句，则返回 NULL。

注意到 CASE 语句是有顺序的，如果一行已经符合了前面的 WHEN，即使后续又满足某个条件也不会再更新。上面的例子中如果将两个 WHEN 调换顺序，则节点 1 在第一个 WHEN 就会被认定为内部节点，不会再改变。

IF 函数

1
IF(expression, "YES", "NO");

如果条件为真，则返回 "YES"，如果条件为假，则返回 "NO"

IFNULL 函数

1
IFNULL(expression1, expression2);

如果expression1不为 NULL，则返回expression1；否则返回expression_2的结果。