您的位置:首页 > 教育 > 锐评 > SQL - CTE

SQL - CTE

2024/10/5 18:30:23 来源:https://blog.csdn.net/qq_29720657/article/details/139840567  浏览:    关键词: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_nameWHERE condition
)
-- 主查询
SELECT column1, column2, ...
FROM cte_name
WHERE condition;

示例

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

WITH DeptAvgSalaries AS (SELECT DepartmentID, AVG(Salary) AS AvgSalaryFROM EmployeesGROUP 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, ManagerIDFROM EmployeesWHERE ManagerID IS NULLUNION ALL-- 递归部分:查找下属员工SELECT e.EmployeeID, e.EmployeeName, e.ManagerIDFROM Employees eINNER 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

直接写成这样:

selectid,tag_name,trace_id,id1,tag_name1,remark
fromdla
order byid asc
limit1000
offset0;

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

  1. 可读性和可维护性

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

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

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

对比实例

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

selectv.id,v.tag_name,v.trace_id,v.id1,v.tag_name1,v.remark
from(selectapi_1.id,api_1.tag_name,api_1.trace_id,api_2.id as id1,api_2.tag_name as tag_name1,api_2.remarkfrom(selectid,tag_name,trace_idfrom(selectid,tag_name,trace_idfrom(selectid,tag_name,trace_id,enabled_flag,remarkfromxxx.tag_recordwhereenabled_flag != 0) vwhereremark not in ('10', '20', '40', '30')group byid,tag_name,trace_id) dm_1) api_1left join (selectid,tag_name,remarkfrom(selectid,tag_name,remark,created_by,trace_id,enabled_flagfromXXX.tag_record) vwhereremark not in ('10', '20', '40', '30')group byid,tag_name,remark) api_2 on api_1.id = api_2.id) v
order byid asc
limit1000
offset0;

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

使用 CTE 的好处

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

withds_1001 as (selectid,tag_name,trace_id,enabled_flag,remarkfromXXXX.tag_recordwhereenabled_flag != 0),dm_1 as (selectid,tag_name,trace_idfromds_1001whereremark not in ('10', '20', '40', '30')group byid,tag_name,trace_id),api_1 as (selectid,tag_name,trace_idfromdm_1),ds_2001 as (selectid,tag_name,remark,created_by,trace_id,enabled_flagfromXXXX.tag_record),dm_2 as (selectid,tag_name,remarkfromds_2001whereremark not in ('10', '20', '40', '30')group byid,tag_name,remark),api_2 as (selectid,tag_name,remarkfromdm_2),dla as (selectapi_1.id,api_1.tag_name,api_1.trace_id,api_2.id as id1,api_2.tag_name as tag_name1,api_2.remarkfromapi_1left join api_2 on api_1.id = api_2.id)
selectid,tag_name,trace_id,id1,tag_name1,remark
fromdla
order byid asc
limit1000
offset0;

总结

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

版权声明:

本网仅为发布的内容提供存储空间,不对发表、转载的内容提供任何形式的保证。凡本网注明“来源:XXX网络”的作品,均转载自其它媒体,著作权归作者所有,商业转载请联系作者获得授权,非商业转载请注明出处。

我们尊重并感谢每一位作者,均已注明文章来源和作者。如因作品内容、版权或其它问题,请及时与我们联系,联系邮箱:809451989@qq.com,投稿邮箱:809451989@qq.com