SQL - CTE

CTE，全称为 Common Table Expression（公用表表达式），是一种 SQL 表达式，用于命名一个临时结果集，这个结果集仅在执行单个查询（包括 SELECT、INSERT、UPDATE 或 DELETE 语句）期间可用。CTE 可以提高 SQL 查询的可读性和结构化程度，特别是在复杂查询中

CTE 的作用和特点：

1. 提高可读性：CTE 允许将复杂查询分解成更小、更易理解的部分。每个 CTE 定义一个结果集，可以在主查询中像使用普通表一样使用。

2. 递归查询：CTE 支持递归查询，可以用于处理层次结构数据，如组织结构、树状结构等。

3. 临时性：CTE 是临时的，仅在执行查询时存在。一旦查询执行完毕，CTE 就不再存在。

CTE 的语法

基本的 CTE 语法如下：

WITH cte_name (column1, column2, ...) AS (
    -- CTE 的定义查询
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
-- 主查询
SELECT column1, column2, ...
FROM cte_name
WHERE condition;

示例

以下是一个简单的 CTE 示例，它从一个员工表中提取数据，并使用 CTE 来计算每个部门的平均工资：

WITH DeptAvgSalaries AS (
    SELECT 
        DepartmentID, 
        AVG(Salary) AS AvgSalary
    FROM 
        Employees
    GROUP BY 
        DepartmentID
)
SELECT 
    e.EmployeeID, 
    e.EmployeeName, 
    e.DepartmentID, 
    d.AvgSalary
FROM 
    Employees e
JOIN 
    DeptAvgSalaries d 
ON 
    e.DepartmentID = d.DepartmentID
WHERE 
    e.Salary > d.AvgSalary;

递归 CTE 示例

递归 CTE 可以用来处理层次结构数据，如公司中的组织结构。以下是一个递归 CTE 示例，它从员工表中获取一个特定员工及其所有下属的层次结构：

WITH RECURSIVE EmployeeHierarchy AS (
    -- 基本部分：从根节点（经理）开始
    SELECT 
        EmployeeID, 
        EmployeeName, 
        ManagerID
    FROM 
        Employees
    WHERE 
        ManagerID IS NULL

    UNION ALL

    -- 递归部分：查找下属员工
    SELECT 
        e.EmployeeID, 
        e.EmployeeName, 
        e.ManagerID
    FROM 
        Employees e
    INNER JOIN 
        EmployeeHierarchy eh 
    ON 
        e.ManagerID = eh.EmployeeID
)
SELECT 
    EmployeeID, 
    EmployeeName, 
    ManagerID
FROM 
    EmployeeHierarchy;

总结

CTE 是一个非常有用的 SQL 工具，特别是在处理复杂查询时。它通过分解查询、提高可读性和支持递归查询，显著增强了 SQL 的功能和灵活性

为什么不直接这样写：

 select
id,
tag_name,
trace_id,
id1,
tag_name1,
remark
from
dla
order by
id asc
limit
1000
offset
0

直接写成这样：

select
  id,
  tag_name,
  trace_id,
  id1,
  tag_name1,
  remark
from
  dla
order by
  id asc
limit
  1000
offset
  0;

当然是可以的，并且这样写确实更简洁。但在某些情况下，分解查询并使用 CTE（公用表表达式）具有以下优势：

1. 可读性和可维护性：

   • 复杂查询如果直接写在一起，会让人难以理解。使用 CTE 可以把大查询拆成多个小查询，每个小查询代表一个逻辑步骤，便于阅读和维护。
   • 比如你提供的SQL中有很多过滤和转换步骤，通过CTE可以逐步展示每个步骤的逻辑。

2. 避免重复：

   • 当同一子查询在查询中多次使用时，CTE 可以避免重复书写这个子查询，减少代码冗余。

3. 递归查询：

   • CTE 支持递归，这对于处理层次结构的数据非常有用。直接写查询很难处理这样的需求。

对比实例

如果我们将整个查询合并为一个大查询，代码可能如下：

select
  v.id,
  v.tag_name,
  v.trace_id,
  v.id1,
  v.tag_name1,
  v.remark
from
  (
    select
      api_1.id,
      api_1.tag_name,
      api_1.trace_id,
      api_2.id as id1,
      api_2.tag_name as tag_name1,
      api_2.remark
    from
      (
        select
          id,
          tag_name,
          trace_id
        from
          (
            select
              id,
              tag_name,
              trace_id
            from
              (
                select
                  id,
                  tag_name,
                  trace_id,
                  enabled_flag,
                  remark
                from
                  xxx.tag_record
                where
                  enabled_flag != 0
              ) v
            where
              remark not in ('10', '20', '40', '30')
            group by
              id,
              tag_name,
              trace_id
          ) dm_1
      ) api_1
      left join (
        select
          id,
          tag_name,
          remark
        from
          (
            select
              id,
              tag_name,
              remark,
              created_by,
              trace_id,
              enabled_flag
            from
              XXX.tag_record
          ) v
        where
          remark not in ('10', '20', '40', '30')
        group by
          id,
          tag_name,
          remark
      ) api_2 on api_1.id = api_2.id
  ) v
order by
  id asc
limit
  1000
offset
  0;

虽然上面的查询也能正确执行，但显得非常复杂，不易于阅读和理解。任何一个部分出现问题或需要修改，都需要在大量的嵌套查询中寻找和调整。

使用 CTE 的好处

通过使用 CTE，可以将每个逻辑步骤分离出来，使得查询结构清晰：

with
  ds_1001 as (
    select
      id,
      tag_name,
      trace_id,
      enabled_flag,
      remark
    from
       XXXX.tag_record
    where
      enabled_flag != 0
  ),
  dm_1 as (
    select
      id,
      tag_name,
      trace_id
    from
      ds_1001
    where
      remark not in ('10', '20', '40', '30')
    group by
      id,
      tag_name,
      trace_id
  ),
  api_1 as (
    select
      id,
      tag_name,
      trace_id
    from
      dm_1
  ),
  ds_2001 as (
    select
      id,
      tag_name,
      remark,
      created_by,
      trace_id,
      enabled_flag
    from
      XXXX.tag_record
  ),
  dm_2 as (
    select
      id,
      tag_name,
      remark
    from
      ds_2001
    where
      remark not in ('10', '20', '40', '30')
    group by
      id,
      tag_name,
      remark
  ),
  api_2 as (
    select
      id,
      tag_name,
      remark
    from
      dm_2
  ),
  dla as (
    select
      api_1.id,
      api_1.tag_name,
      api_1.trace_id,
      api_2.id as id1,
      api_2.tag_name as tag_name1,
      api_2.remark
    from
      api_1
      left join api_2 on api_1.id = api_2.id
  )
select
  id,
  tag_name,
  trace_id,
  id1,
  tag_name1,
  remark
from
  dla
order by
  id asc
limit
  1000
offset
  0;

总结

直接合并查询可以减少一些代码行数，但对于复杂查询，使用 CTE 可以显著提高可读性、可维护性和代码复用性。特别是在需要递归查询时，CTE 更是不可或缺的工具。因此，根据具体情况选择是否使用 CTE 是非常重要的