MySQL递归查询
CTE (Common Table Expression)
CTE（公用表达式）是一个临时结果的集合，作用域为单次查询
WITH [RECURSIVE]
cte_name [(col_name [, col_name] ...)] AS (subquery)
[, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
- cte_name: cte名称，后续使用
- col_name: 列名，未指定时从subquery中推断
- subquery：subquery部分产生结果集，括号为必须
WITH cte (col1, col2) AS
(
SELECT 1, 2
UNION ALL
SELECT 3, 4
)
SELECT col1, col2 FROM cte;
+------+------+
| col1 | col2 |
+------+------+
| 1 | 2 |
| 3 | 4 |
+------+------+
RECURSIVE CTE
基本递归查询
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
- WITH 后必须包含RECURISIVE
- subquery中，
SELECT 1为初始查询（递归入口），SELECT n + 1 FROM cte WHERE n < 5为递归部分，查询终止条件（递归出口）为递归部分结果为空; 每次迭代只会处理上一次迭代产生的结果
其他查询
CREATE DATABASE IF NOT EXISTS test;
CREATE TABLE IF NOT EXISTS test.node (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(64) NOT NULL DEFAULT '',
`parent_id` INT NOT NULL DEFAULT 0,
PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;
INSERT INTO test.node (id, name, parent_id)
VALUES(1, "node1", 0);
INSERT INTO test.node (id, name, parent_id)
VALUES(2, "node2", 1);
INSERT INTO test.node (id, name, parent_id)
VALUES(3, "node3", 1);
INSERT INTO test.node (id, name, parent_id)
VALUES(4, "node4", 2);
INSERT INTO test.node (id, name, parent_id)
VALUES(5, "node5", 2);
INSERT INTO test.node (id, name, parent_id)
VALUES(6, "node6", 5);
select * from test.node;
+----+-------+-----------+
| id | name | parent_id |
+----+-------+-----------+
| 1 | node1 | 0 |
| 2 | node2 | 1 |
| 3 | node3 | 1 |
| 4 | node4 | 2 |
| 5 | node5 | 2 |
| 6 | node6 | 5 |
+----+-------+-----------+
查询节点路径（自顶向下）
WITH RECURSIVE cte AS (
SELECT id,
name,
0 AS depth
FROM test.node
WHERE id = 1
UNION ALL
SELECT t.id,
CONCAT(cte.name, '->', t.name),
cte.depth + 1
FROM cte
INNER JOIN test.node AS t ON cte.id = t.parent_id
)
SELECT *
FROM cte;
+------+----------------------------+-------+
| id | name | depth |
+------+----------------------------+-------+
| 1 | node1 | 0 |
| 2 | node1->node2 | 1 |
| 3 | node1->node3 | 1 |
| 4 | node1->node2->node4 | 2 |
| 5 | node1->node2->node5 | 2 |
| 6 | node1->node2->node5->node6 | 3 |
+------+----------------------------+-------+
查询所有子节点个数（自底向上）
WITH RECURSIVE cte AS (
SELECT parent_id
FROM test.node
UNION ALL
SELECT t.parent_id
FROM cte
INNER JOIN test.node AS t ON cte.parent_id = t.id
)
SELECT parent_id,
count(*) AS cnt
FROM cte
GROUP BY parent_id;
+-----------+-----+
| parent_id | cnt |
+-----------+-----+
| 0 | 6 |
| 1 | 5 |
| 2 | 3 |
| 5 | 1 |
+-----------+-----+