Mysql CTE语法以及使用场景

doMore 241 2024-04-12

前情

在之前组装树形结构时，常用如下方式。这种方式会非常频繁的去和数据库交互，花费在连接上的时间甚至会比实际执行查询的时间还要久。

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 有一些优势：

  1. 派生表只能在查询中被引用一次。而 CTE 可以被多次引用。要使用派生表结果的多个实例，必须多次派生结果。

  2. CTE 可以自引用（递归）。

  3. 一个 CTE 可以引用另一个 CTE。

  4. 当 CTE 的定义出现在语句的开头而不是嵌入其中时，可能更容易阅读。

mysql