新闻中心

使用Common Table Expressions (CTE) 简化复杂查询

2025-09-10
浏览次数：次
返回列表
CTE通过模块化分解复杂查询，提升可读性与维护性，适用于多步骤处理、递归层级遍历等场景，相比子查询更易复用和调试，但性能优化仍依赖索引、过滤和执行计划合理性。

使用common table expressions (cte) 简化复杂查询

使用Common Table Expressions (CTE) 确实是简化复杂SQL查询的一剂良药。它允许我们将一个庞大、难以理解的查询分解成多个逻辑上独立的、命名的子查询，这些子查询可以被后续的CTE或主查询引用。这种模块化的方式极大地提升了查询的可读性、可维护性，甚至在某些特定场景下能帮助我们更清晰地构建逻辑，避免深层嵌套子查询的噩梦。

在我的日常工作中，尤其面对那些需要多步骤数据处理、聚合后再过滤，或者涉及层级结构数据的查询时，CTE几乎成了我的首选工具。它就像是给复杂查询搭建了一个清晰的脚手架，每一步操作都有其明确的名称和职责，让原本纠缠不清的逻辑变得一目了然。

-- 假设我们有一个Orders表和Customers表
-- 目标：找出过去一年中，购买总金额超过所有客户平均购买总金额的客户，
-- 并列出他们最近一笔订单的详情。

-- 没有CTE，你可能会看到这样的查询，或者更复杂的嵌套：
SELECT
    c.CustomerID,
    c.CustomerName,
    o_latest.OrderID,
    o_latest.OrderDate,
    o_latest.OrderTotal
FROM
    Customers c
JOIN
    (SELECT CustomerID, MAX(OrderDate) AS LatestOrderDate
     FROM Orders
     WHERE OrderDate >= DATEADD(year, -1, GETDATE())
     GROUP BY CustomerID) AS latest_orders_summary
ON c.CustomerID = latest_orders_summary.CustomerID
JOIN
    Orders o_latest ON latest_orders_summary.CustomerID = o_latest.CustomerID
                   AND latest_orders_summary.LatestOrderDate = o_latest.OrderDate
WHERE
    c.CustomerID IN (
        SELECT CustomerID
        FROM (
            SELECT CustomerID, SUM(OrderTotal) AS TotalSpent
            FROM Orders
            WHERE OrderDate >= DATEADD(year, -1, GETDATE())
            GROUP BY CustomerID
        ) AS customer_spending
        WHERE TotalSpent > (
            SELECT *G(TotalSpent)
            FROM (
                SELECT SUM(OrderTotal) AS TotalSpent
                FROM Orders
                WHERE OrderDate >= DATEADD(year, -1, GETDATE())
                GROUP BY CustomerID
            ) AS all_customer_spending_*g
        )
    );

-- 使用CTE，同样的逻辑变得清晰得多：
WITH RecentOrders AS (
    -- 1. 筛选出过去一年的订单
    SELECT
        CustomerID,
        OrderID,
        OrderDate,
        OrderTotal
    FROM
        Orders
    WHERE
        OrderDate >= DATEADD(year, -1, GETDATE())
),
CustomerSpending AS (
    -- 2. 计算每个客户在过去一年的总消费
    SELECT
        CustomerID,
        SUM(OrderTotal) AS TotalSpent,
        MAX(OrderDate) AS LastOrderDate
    FROM
        RecentOrders
    GROUP BY
        CustomerID
),
AverageOverallSpending AS (
    -- 3. 计算所有客户的平均总消费
    SELECT
        *G(TotalSpent) AS AvgTotalSpent
    FROM
        CustomerSpending
),
HighValueCustomers AS (
    -- 4. 找出总消费超过平均值的客户
    SELECT
        cs.CustomerID,
        cs.TotalSpent,
        cs.LastOrderDate
    FROM
        CustomerSpending cs
    CROSS JOIN
        AverageOverallSpending aos
    WHERE
        cs.TotalSpent > aos.AvgTotalSpent
)
-- 5. 最后，联结回原始订单和客户信息，获取这些高价值客户的最新订单详情
SELECT
    c.CustomerID,
    c.CustomerName,
    ro.OrderID,
    ro.OrderDate,
    ro.OrderTotal
FROM
    Customers c
JOIN
    HighValueCustomers hvc ON c.CustomerID = hvc.CustomerID
JOIN
    RecentOrders ro ON hvc.CustomerID = ro.CustomerID AND hvc.LastOrderDate = ro.OrderDate;

通过上述对比，不难看出CTE如何将一个复杂问题拆解成一系列逻辑步骤。每个CTE都专注于完成一个特定的任务，它的输出可以作为下一个CTE的输入，最终汇聚成我们想要的最终结果。这种“分而治之”的策略，在我看来，是编写高质量、易于理解和维护SQL代码的关键。

CTE与子查询（Subquery）有什么区别？

这个问题经常被提起，因为它们在某种程度上都能实现查询的模块化。然而，它们之间存在几个关键的、值得深思的差异。子查询，特别是嵌套子查询，通常被视为查询的一部分，它的结果集在外部查询中被直接使用，并且通常是匿名且一次性的。当子查询层层嵌套时，代码的可读性会急剧下降，调试起来更是令人头疼。

CTE则不同，它通过

WITH
子句明确地命名一个临时结果集。这个命名不仅提升了可读性，更重要的是，一个CTE可以在同一个查询中被多次引用，这在某种程度上实现了代码的复用。想象一下，如果一个复杂的计算结果需要在查询的不同部分被多次使用，使用CTE可以避免重复编写相同的逻辑。此外，CTE的作用域仅限于紧随其后的那个
SELECT
, 
INSERT
, 
UPDATE
, 
DELETE
或 
CREATE VIEW
语句。这意味着它不会像临时表那样在整个会话中持续存在，也不会造成数据库对象的污染。还有一个子查询无法比拟的独特之处，就是CTE能够支持递归查询，这在处理层级结构数据时是不可或缺的利器。

从我个人的经验来看，当查询逻辑需要多于一个步骤，或者某个中间结果需要在查询的不同位置被引用时，CTE几乎总是比子查询更优的选择。它迫使你以一种更结构化、更清晰的方式思考你的数据处理流程。

CTE在递归查询中的应用是怎样的？

递归CTE是Common Table Expressions中最强大也最令人兴奋的特性之一，它让SQL在处理层级数据方面变得异常灵活和高效。例如，组织架构图、物料清单（BOM）、社交网络中的朋友关系链等，这些都属于典型的层级或图结构数据，而递归CTE正是解决这类问题的理想工具。

一个递归CTE通常由两个主要部分组成：

FashionLabs FashionLabs

AI服装模特、商品图，可商用，低价提升销量神器

FashionLabs 86 查看详情 FashionLabs
  1. 锚定成员（Anchor Member）：这是递归的起点，它是一个非递归的查询，用于建立递归的基础。它定义了递归过程中的初始行集。
  2. 递归成员（Recursive Member）：这是递归的主体，它引用了CTE自身，并基于锚定成员或上一次递归的结果来生成新的行。每次迭代都会处理上一次迭代产生的新行，直到不再有新的行产生。

这两个部分通过

UNION ALL
连接起来。数据库系统会先执行锚定成员，然后反复执行递归成员，每次都使用前一次迭代的结果作为输入，直到递归成员不再返回任何行。

举个例子，假设我们有一个

Employees
表，其中包含
EmployeeID
、
EmployeeName
和
ManagerID
，我们想找出某个员工的所有下属（包括直接和间接下属），并显示他们所在的层级。

WITH EmployeeHierarchy AS (
    -- 锚定成员：找到某个特定员工（例如，EmployeeID = 101 的员工）
    SELECT
        EmployeeID,
        EmployeeName,
        ManagerID,
        0 AS Level -- 初始层级为0
    FROM
        Employees
    WHERE
        EmployeeID = 101 -- 从这个员工开始向下查找

    UNION ALL

    -- 递归成员：找到上一层级员工的直接下属
    SELECT
        e.EmployeeID,
        e.EmployeeName,
        e.ManagerID,
        eh.Level + 1 AS Level -- 层级加1
    FROM
        Employees e
    JOIN
        EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID -- 联结到CTE自身
)
SELECT
    EmployeeID,
    EmployeeName,
    Level
FROM
    EmployeeHierarchy
ORDER BY
    Level, EmployeeName;

这个例子清晰地展示了递归CTE如何从一个起点出发，一步步地向下遍历层级结构。锚定成员提供了起始点，而递归成员则定义了如何从当前层级扩展到下一层级。这种能力是普通子查询或视图无法实现的，它赋予了SQL处理复杂数据关系以全新的维度。

CTE的性能优化策略有哪些？

谈到CTE的性能，这其实是一个比较微妙的话题。很多人会误以为使用CTE会天然地带来性能提升，但实际上，CTE本身更多是一种逻辑组织和代码可读性的工具，它并不能神奇地改变底层查询的执行计划。数据库优化器在处理CTE时，通常会将其“展开”或“内联”到主查询中，然后像处理普通查询一样进行优化。这意味着，一个编写糟糕的CTE查询，其性能可能和等效的糟糕子查询一样差。

然而，这不代表我们不能通过一些策略来“优化”CTE的性能：

  1. 确保内部查询高效：CTE的性能基础是其内部定义的查询是否高效。如果CTE内部的
    SELECT
    语句涉及大量数据扫描、复杂的联结或聚合，那么无论它是否被封装在CTE中，都会是性能瓶颈。所以，确保CTE内部使用的表有合适的索引，联结条件合理，过滤条件有效，这才是根本。
  2. 避免不必要的复杂性：不要为了使用CTE而使用CTE。如果一个简单的子查询就能完成任务，那么就没有必要将其封装成一个CTE。过度细分CTE有时反而会增加优化器的负担，因为它需要处理更多的逻辑单元。
  3. 理解优化器的行为：在某些数据库系统中（例如SQL Server），CTE默认情况下不会被“物化”（materialized），即不会在内存或磁盘上创建一个临时的物理表来存储CTE的结果。优化器会尝试将其与主查询合并处理。但有时，如果一个CTE非常复杂且被多次引用，数据库可能会选择物化它。了解你的数据库系统如何处理CTE，可以通过
    EXPLAIN
    或
    SHOW PLAN
    等工具查看执行计划，这能帮助你判断CTE是否被有效利用。
  4. 谨慎处理大结果集：如果一个CTE会产生一个非常大的中间结果集，而这个结果集又被后续查询多次引用，那么考虑将其结果先存入一个临时表或表变量，有时反而能获得更好的性能。这是因为临时表会被物理写入磁盘或内存，其上的索引可以被后续查询利用，避免了重复计算。
  5. 合理运用过滤：尽可能早地在CTE内部进行数据过滤。减少传递给下一个CTE或主查询的数据量，是提升性能的黄金法则。不要在最终的
    SELECT
    语句中才过滤掉大量中间结果，这会导致不必要的计算。

总的来说，CTE的性能优化更多地是关于如何编写高效的SQL查询，而不是CTE本身有什么特殊的魔法。它提供了一个清晰的框架，让我们能更好地组织和理解复杂逻辑，而一个清晰的逻辑往往是性能优化的第一步。

以上就是使用Common Table Expressions (CTE) 简化复杂查询的详细内容，更多请关注其它相关文章！


# 遍历  # 找面料平台网站排名优化  # 江西抖音搜索优化seo  # seo优化中心  # google的seo怎么做  # 德宏seo优化费用  # 芜湖网站推广视频  # 抖音seo系统方式  # 簇桥营销推广  # 网站seo优化方案策化书笔记  # A闲鱼营销推广人xk001522  # 迭代  # 忘记密码  # 数据处理  # mysql教程  # 有什么  # 这是  # 操作流程  # 将其  # 离线  # 递归  # lsp  # 代码可读性  # 作用域  # 社交网络  # 区别  # ai  # 工具 


相关栏目： 【 科技资讯46185 】 【 网络学院92790 】


相关推荐： 4399免费游戏网址入口 4399小游戏免费入口点开即玩  Python多版本共存与虚拟环境管理深度指南  sublime怎么设置启动时打开的窗口_sublime会话管理与热退出  学习通在线学习平台 学习通网页版直接进入课程中心  mysql通配符支持数字匹配吗_mysql通配符能否用于数字匹配的解析  C++如何实现异步操作_C++11使用std::future和std::async进行异步编程  蓝湖怎样用切图标注提对接效率_蓝湖用切图标注提对接效率【设计对接】  如何使用Node.js csv 包按条件移除含空字段的CSV记录  PrimeNG Sidebar背景色自定义指南：CSS覆盖与主题化实践  在J*a中如何隐藏复杂性_使用门面模式组织对象交互  Golang如何实现容器化日志收集与分析_Golang容器日志收集分析方法  b站赚钱渠道_b站收益来源  C++ typeid如何获取类型信息_C++ RTTI运行时类型识别用法  在Pyomo中实现基于变量的条件约束：Big-M方法详解  excel怎么制作工资条 excel快速生成工资条的方法  CSS Grid如何控制元素对齐_align-items与justify-items组合使用  免费抖音短视频入口_抖音网页版短视频免费通道  Yandex免登录官网入口_俄罗斯Yandex搜索引擎直达链接  AO3官方在线访问地址 Archive of Our Own最新镜像合集  Golang如何通过reflect获取匿名字段方法_Golang reflect匿名字段方法访问技巧  提升Kafka消费者健壮性：会话超时处理与消息处理语义  C++如何进行游戏物理模拟_使用Box2D库为C++游戏添加2D物理效果  内存检查：在VS Code中调试C++时的内存视图  J*aScript教程：根据元素文本内容动态设置背景色  钉钉视频会议声音异常如何处理 钉钉会议音频修复技巧  护手霜蹭到袖口上了如何清洗？ 怎样避免留下一圈油印？  windows10怎么查看本机ip_windows10命令提示符ipconfig使用  MAC如何安全彻底地删除文件_MAC使用终端命令确保文件无法被恢复  vivo浏览器怎么扫描二维码 vivo浏览器内置扫一扫功能使用方法  拷贝漫画电脑版官网入口 拷贝漫画(PC版)在线直达  俄罗斯搜索引擎Yandex指南 附2025年免登录官网入口  vivo浏览器自带的下载器速度慢怎么办 vivo浏览器提升文件下载速度的技巧  机器学习中对数变换预测结果的反向还原  漫蛙Manwa2官网入口地址分享 漫蛙漫画PC版永久访问通道  大麦的“候补”是什么意思 大麦候补购票规则【详解】  Go与Ruby之间实现AES加密互通：CFB模式下的密钥长度匹配策略  Win10系统服务哪些可以禁用 Win10安全优化服务列表【干货】  限制HTML日期输入框的日期选择范围  解决 MongoDB 聚合查询中对象数组 _id 匹配问题  QQ邮箱官网登录入口 QQ邮箱网页版邮箱快速登录  Go调试环境为何无法启动_Go调试器启动失败原因与解决策略  C++如何实现线程池_C++11手动实现一个简单的固定大小线程池  海棠电脑版入口_通过电脑访问海棠官网阅读  优化 Jest 模拟：强制未实现函数抛出错误以提升测试效率  《噬血代码2》新预告片发布 展示游戏剧情  Golang如何实现状态模式管理对象状态_Golang State模式实现技巧  台积电1.4nm工艺A14瞄准2028：10年来性能提升80%  12306选座怎么选到临时改签座_12306改签选座策略与步骤  一加手机拍照效果不好怎么办 一加哈苏影像调校与专业模式使用教程【高手篇】  Composer的 "licenses" 命令如何帮助你遵守开源协议_检查项目依赖的许可证合规性 

上一篇：最左前缀原则在MySQL联合索引中是如何应用的？
下一篇：什么场景下你会选择NoSQL而非MySQL？

搜索