使用CTE（公用表表达式）简化复杂查询逻辑

檐角小猫

已于 2025-01-08 15:47:23 修改

阅读量771

点赞数 14

文章标签： sql 数据库

于 2025-01-08 15:10:55 首次发布

本文链接：https://blog.youkuaiyun.com/m0_65776770/article/details/145009624

版权

使用CTE（公用表表达式）可以显著简化复杂查询逻辑，提高SQL代码的可读性和可维护性。CTE允许你定义一个临时结果集，该结果集在定义语句的执行范围内可以被引用和使用。以下是一些实例和实践，展示如何利用CTE来简化复杂的查询：

基本概念

CTE的语法如下：

WITH CTE_name (column1, column2, ...) AS (
    -- CTE 计算逻辑
    SELECT ...
)
-- 使用该临时结果集进行其他操作
SELECT * FROM CTE_name WHERE ...

注意：

在使用WITH定义CTE时，该语句的前一个语句必须以 “ ；” 结尾。

实例与应用

1. 分析员工绩效

假设我们有一张EmployeeSales表，我们想要找出每个员工销售额超过公司平均水平的月数。

示例：

WITH SalesAvg AS (
    SELECT EmployeeID, MONTH(SaleDate) as SaleMonth, SUM(SaleAmount) as TotalSales
    FROM EmployeeSales
    GROUP BY EmployeeID, MONTH(SaleDate)
),
CompanyAvg AS (
    SELECT AVG(TotalSales) as AvgSales, SaleMonth
    FROM SalesAvg
    GROUP BY SaleMonth
)
SELECT s.EmployeeID, COUNT(*) as MonthsAboveAvg
FROM SalesAvg s
JOIN CompanyAvg c ON s.SaleMonth = c.SaleMonth AND s.TotalSales > c.AvgSales
GROUP BY s.EmployeeID;

解析：

SalesAvg：计算每位员工每月的总销售额。
CompanyAvg：计算全公司每个月的平均销售额。
最终查询通过连接两者，筛选出员工销售高于平均值的月份。

2. 层次结构查询（例如组织架构）

大多数层次结构可以利用递归CTE进行处理。例如，你有一张部门表，每条记录包含一个指向其父部门的外键。

示例：

WITH DeptHierarchy (DeptID, ParentDeptID, DeptName, Level) AS (
    SELECT DeptID, ParentDeptID, DeptName, 0 AS Level 
    FROM Departments 
    WHERE ParentDeptID IS NULL
    
    UNION ALL
    
    SELECT d.DeptID, d.ParentDeptID, d.DeptName, h.Level + 1 
    FROM Departments d 
    INNER JOIN DeptHierarchy h ON d.ParentDeptID = h.DeptID
)
SELECT * FROM DeptHierarchy;

解析：

初始部分选择根节点（即没有父部门）。
递归将子部门结合进来，并增加层级深度。

3. 多步骤数据加工流程

当需要经过多个步骤变换数据时，可以分开写入多个CTE使得逻辑更清晰。例如先过滤，再聚合，最后排序。

示例：

WITH FilteredOrders AS (
    SELECT OrderID, CustomerID, OrderTotal 
    FROM Orders 
    WHERE OrderDate >= '2023-01-01'
),
AggregatedOrders AS (
    SELECT CustomerID, SUM(OrderTotal) as TotalSpent 
    FROM FilteredOrders 
    GROUP BY CustomerID
)
SELECT *
FROM AggregatedOrders 
ORDER BY TotalSpent DESC;

解析：

首先，用FilteredOrders得到符合条件的订单。
然后，通过AggregatedOrders汇总这些订单的数据。
最后，在主查询中获取并按需求排列。

4.灵活实现SQL需求

由于在SQL Server中，DELETE语句不支持直接将TOP子句与ORDER BY一起使用。但存在SELECT TOP 1 的语法。

示例：

DELETE TOP (1) FROM Custom_barcodetemp
WHERE fbarcode = @fbarcode AND ftype = @ftype AND fmachinenum = @fmachinenum 
ORDER BY fcreatetime DESC;

这是错误的做法，那我们只能先将满足条件的第一条数据查出来并使用CTE进行封装，再通过DELETE语句进行删除。

WITH CTE AS (
    SELECT TOP 1 *
    FROM Custom_barcodetemp
    WHERE fbarcode = @fbarcode AND ftype = @ftype AND fmachinenum = @fmachinenum
    ORDER BY fcreatetime DESC
	)

DELETE FROM Custom_barcodetemp
WHERE fbarcode = @fbarcode
	  AND ftype = @ftype
	  AND fmachinenum = @fmachinenum
	  AND fcreatetime = (SELECT fcreatetime FROM CTE);

这样就能实现删除创建时间为最新的第一条数据了。