CTE在分组场景的核心价值是提前提炼分组逻辑供主查询复用，而非直接执行汇总；必须在CTE内完成全部聚合，主查询仅轻量关联，否则性能受损且成果被绕过。
CTE 本身不执行分组汇总，它只是把分组逻辑“提前提炼出来”，让主查询能干净地复用结果——这才是它在分组场景下真正不可替代的价值。
为什么不能直接在 CTE 里写 GROUP BY 后再 JOIN？
很多人试图这样写：
WITH sales_agg AS ( SELECT SalesPersonID, COUNT(*) AS cnt FROM Sales.SalesOrderHeader GROUP BY SalesPersonID ), order_detail_agg AS ( SELECT SalesOrderID, SUM(OrderQty) AS total_qty FROM Sales.SalesOrderDetail GROUP BY SalesOrderID ) SELECT s.SalesPersonID, s.cnt, d.total_qty FROM sales_agg s JOIN Sales.SalesOrderHeader h ON s.SalesPersonID = h.SalesPersonID JOIN order_detail_agg d ON h.SalesOrderID = d.SalesOrderID;
问题在于：主查询又把 SalesOrderHeader 拉进来做 JOIN，等于绕过了 sales_agg 的聚合成果，中间结果集可能暴涨数倍。CTE 的价值被完全浪费。
正确做法是：所有聚合必须在 CTE 内完成，主查询只做轻量关联或过滤。
- CTE 中的
GROUP BY必须覆盖最终需要的所有分组维度（比如既要按人、又要按年，就得在 CTE 里一起分） - 如果主表小、明细表大（如 orders 表 10 万行，order_items 表 800 万行），先在 CTE 里对
order_items按order_id聚合，再 JOINorders，性能提升往往超过 5 倍 - SQL Server 不会自动物化 CTE；若同一个 CTE 被引用多次（如既用于 JOIN 又用于 WHERE EXISTS），优化器可能重复执行其内部查询——这时应考虑用临时表替代
多个分组逻辑共存时怎么组织 CTE？
当你要同时计算「每个销售员的订单数」「每个销售员的退货数」「每个销售员的平均订单金额」，别堆在一个 CTE 里硬塞所有字段，容易失控。
推荐分层定义：
WITH sales_orders AS ( SELECT SalesPersonID, COUNT(*) AS order_cnt, AVG(TotalDue) AS avg_order_amt FROM Sales.SalesOrderHeader WHERE Status = 5 -- 已发货 GROUP BY SalesPersonID ), sales_returns AS ( SELECT SalesPersonID, COUNT(*) AS return_cnt FROM Sales.SalesOrderHeader h JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID WHERE d.ProductID IN (SELECT ProductID FROM Production.Product WHERE FinishedGoodsFlag = 0) GROUP BY SalesPersonID ) SELECT o.SalesPersonID, o.order_cnt, ISNULL(r.return_cnt, 0) AS return_cnt, o.avg_order_amt FROM sales_orders o LEFT JOIN sales_returns r ON o.SalesPersonID = r.SalesPersonID;
- 每个 CTE 职责单一，调试和修改都只影响局部
-
sales_returns里嵌套了子查询，但这是允许的；CTE 内部可以含任意合法 SELECT，只要不出现在递归成员中 - 列名列表
(SalesPersonID, order_cnt, ...)在多 CTE 场景下建议显式写出，避免因字段顺序错位导致静默错误
递归 CTE 里能做分组汇总吗？
不能。SQL Server 明确禁止在递归成员中使用 GROUP BY、SUM()、COUNT()、DISTINCT 等聚合操作。
典型错误写法：
WITH dept_tree AS ( SELECT DepartmentID, ParentID, 1 AS Level FROM HumanResources.Department WHERE ParentID IS NULL UNION ALL SELECT d.DepartmentID, d.ParentID, t.Level + 1 FROM HumanResources.Department d INNER JOIN dept_tree t ON d.ParentID = t.DepartmentID GROUP BY d.DepartmentID, d.ParentID -- ❌ 报错：递归成员中不允许 GROUP BY )
正确路径永远是两步分离：
- 第一步：用递归 CTE 展开完整层级关系（例如生成
RootID → DescendantID映射表） - 第二步：用这个结果 JOIN 员工表，再在外层
GROUP BY RootID汇总人数或薪资 - 锚点成员和递归成员之间必须用
UNION ALL，且递归成员的列数、类型、顺序必须与锚点严格一致
CTE 分组后怎么更新原始表？
CTE 可以作为 UPDATE、DELETE 的目标，但前提是 CTE 引用的必须是单个基表，且不能含聚合、DISTINCT、窗口函数等不可更新元素。
比如你想把每个部门的员工数写回 Department 表的 EmployeeCount 字段：
WITH dept_emp_count AS ( SELECT d.DepartmentID, COUNT(e.BusinessEntityID) AS emp_cnt FROM HumanResources.Department d LEFT JOIN HumanResources.EmployeeDepartmentHistory e ON d.DepartmentID = e.DepartmentID GROUP BY d.DepartmentID ) UPDATE d SET d.EmployeeCount = c.emp_cnt FROM HumanResources.Department d INNER JOIN dept_emp_count c ON d.DepartmentID = c.DepartmentID;
- 这里
dept_emp_count是纯聚合 CTE，不能直接UPDATE dept_emp_count—— 它不是可更新的视图 - 必须通过
JOIN回原始表Department，再在UPDATE子句中指定目标 - 若 CTE 包含
JOIN多个表或聚合，就只能用于SELECT，别指望它能当更新入口
最易被忽略的一点：CTE 的生命周期只限于紧随其后的那一条语句。写完 WITH ... AS (...) SELECT ... 之后，那个名字就彻底失效了——想复用，要么重写，要么转成临时表或视图。
