SQL中CTE概念

最新推荐文章于 2025-04-16 08:50:15 发布

luojinyao2021

最新推荐文章于 2025-04-16 08:50:15 发布

阅读量1.1k

点赞数 19

文章标签：数据库 sql

本文链接：https://blog.youkuaiyun.com/qq_42457295/article/details/145238247

版权

在 SQL 中，CTE 是 Common Table Expression（公共表表达式）的缩写。

一、CTE 的基本概念

定义
- CTE 是一种临时的结果集，它在查询中定义，并且只在查询执行期间存在。它类似于一个临时表，但它的作用域仅限于定义它的查询语句。CTE 可以在 SELECT、INSERT、UPDATE、DELETE 等语句中使用。
- 例如，假设有一个员工表（employees），我们想统计每个部门的员工人数。可以使用 CTE 来实现：
  
  sql复制
```
WITH DepartmentEmployeeCount AS (
    SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id
)
SELECT *
FROM DepartmentEmployeeCount;
```
  在这个例子中，DepartmentEmployeeCount 就是一个 CTE。它首先对员工表进行分组统计，得到每个部门的员工人数，然后在外部的 SELECT 语句中引用这个 CTE，查询出所有部门的员工人数信息。
语法结构
- CTE 的基本语法格式为：
  
  sql复制
```
WITH CTE_name (column1, column2, ...)
AS
(
    -- CTE 查询定义
    SELECT column1, column2, ...
    FROM some_table
    WHERE some_condition
)
-- 主查询
SELECT *
FROM CTE_name;
```
  其中，“CTE_name”是公共表表达式的名称，用于在主查询中引用它；“(column1, column2, ...)”是可选的列名列表，如果省略，SQL 会自动从 CTE 查询定义中的 SELECT 子句获取列名。在 AS 后面的括号内是 CTE 的查询定义，它是一个标准的 SELECT 语句。最后，在主查询中使用 CTE 名称来引用这个临时的结果集。

二、CTE 的特点和优势

提高查询可读性
- 当查询逻辑比较复杂，包含多个子查询或者多表连接时，使用 CTE 可以将复杂的查询分解成多个简单的部分。每个 CTE 可以清晰地表达一个特定的逻辑，使得整个查询更容易理解和维护。例如，在处理多层嵌套的子查询时，CTE 可以将每一层子查询转换为一个单独的 CTE，让查询结构更加清晰。
可重用性
- 在同一个查询中，CTE 可以被多次引用。这避免了重复编写相同的查询逻辑。比如在上面提到的部门员工人数统计的例子中，如果在主查询中需要多次使用这个部门员工人数的结果集，就可以直接多次引用 CTE，而不需要重复编写分组统计的代码。
递归查询
- CTE 支持递归查询，这是它的一个强大功能。递归 CTE 可以用来处理层次结构数据，如组织架构、文件目录等。例如，要查询一个员工的所有下属（包括直接下属和间接下属），可以使用递归 CTE：
  
  sql复制
```
WITH RecursiveEmployee AS (
    SELECT employee_id, manager_id
    FROM employees
    WHERE employee_id = 1 -- 假设 1 是某个员工的 ID，作为递归的起点
    UNION ALL
    SELECT e.employee_id, e.manager_id
    FROM employees e
    INNER JOIN RecursiveEmployee re ON e.manager_id = re.employee_id
)
SELECT *
FROM RecursiveEmployee;
```
  在这个递归 CTE 中，首先选取员工 ID 为 1 的记录作为初始结果集，然后通过 UNION ALL 与自身连接，不断查找下一层的下属，直到没有更多的记录可以添加到结果集中，从而得到所有下属的信息。