前情
在之前组装树形结构时，常用如下方式。这种方式会非常频繁的去和数据库交互，花费在连接上的时间甚至会比实际执行查询的时间还要久。
private List<SysDeptChildVo> findChildMenu(Long deptId) {
String sql = "SELECT t.* from sys_dept t where t.parent_id = ? ";
List<SysDeptChildVo> sysDeptChildVoList = ormJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SysDeptChildVo.class), deptId);
sysDeptChildVoList.forEach(v -> {
v.setChildren(
findChildMenu(v.getDeptId())
);
});
return sysDeptChildVoList;
}
CTE 介绍
公共表表达式（Common table expression，CTE）是一个已命名的临时结果集，它存在于单条语句的范围内，可以在该语句的稍后部分（可能是多次）引用。
通用写法
要指定常用表的表达式，可使用 WITH 子句，其中包含一个或多个以逗号分隔的子句。每个子句提供一个产生结果集的子查询，并为子查询关联一个名称。下面的示例在 WITH 子句中定义了名为 cte1 和 cte2 的 CTE，并在 WITH 子句后面的顶级 SELECT 中引用了它们：
WITH
cte1 AS (SELECT a, b FROM table1),
cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;
-
在包含 WITH 自居的语句中，可以引用每个 CTE 名称来访问相应的 CTE 结果集
-
一个 CTE 名称 可以在多个 CTE 中引用，这样就可以根据简单的 CTE 来定义相对复杂的，但是从阅读上并不会复杂
-
CTE 可以应用自身来定义递归 CTE。常见的有 树形结构。
语法定义如下：
with_clause:
WITH [RECURSIVE]
cte_name [(col_name [, col_name] ...)] AS (subquery)
[, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
-- AS 的子查询部分称为 "CTE 的子查询"，它产生 CTE 结果集。AS 后面的括号是必需的。
并且可以返回指定列：
-- 如果 CTE 名称后面有一个括号内的名称列表，这些名称就是列名，多个结果集时必须是相同的字段。
WITH cte (col1, col2) AS
(
SELECT 1, 2
UNION ALL
SELECT 3, 4
)
SELECT col1, col2 FROM cte;
递归写法
递归共用表表达式是指一个子查询指向其自身名称的表达式。 RECURSIVE 在递归写法中是必须注明的。
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 |
+------+
递归写法分为两个部分： UNION ALL or UNION [DISTINCT]
第一个 SELECT 产生 CTE 的初始行，但不引用 CTE 名称。第二个 SELECT 会产生更多记录，并通过在 FROM 子句中引用 CTE 名称进行递归。当这部分不再产生新记录时，递归结束。
递归实际例子
-- 查询 指定部门所有的子级部门
with recursive child_list as (select sd.*
from sys_dept sd
where sd.parent_id in (1)
union all
select sd.*
from child_list cl
inner join sys_dept sd on cl.dept_id = sd.parent_id)
select *
from child_list
;
-- 查询指定部门的上级部门
-- 例如给定： 乡宁县综治中心
-- 查询结果集： 山西省综治中心 临汾市综治中心 乡宁县综治中心
with recursive parent_list as (select sd.*
from sys_dept sd
where sd.dept_id in (3, 4)
union all
select sd.*
from parent_list pl
inner join sys_dept sd on pl.parent_id = sd.dept_id)
select distinct *
from parent_list
;
CTE 和 子查询对比
公共表表达式(cte)在某些方面类似于 子查询派生表:
-
这两种都命名了。
-
这两种都适用于单个语句的作用域。
一个简单的例子，这两个语句是等价的:
WITH cte AS (SELECT 1) SELECT * FROM cte;
SELECT * FROM (SELECT 1) AS dt;
不过与派生表相比CTE 有一些优势：
-
派生表只能在查询中被引用一次。而 CTE 可以被多次引用。要使用派生表结果的多个实例，必须多次派生结果。
-
CTE 可以自引用（递归）。
-
一个 CTE 可以引用另一个 CTE。
-
当 CTE 的定义出现在语句的开头而不是嵌入其中时，可能更容易阅读。