如何在SQL Server中利用APPLY子句改写复杂的嵌套子查询-SQL-PHP中文网

apply比相关子查询更可控：cross apply等效inner join，outer apply等效left join；右侧须返回表结构、禁用多表引用、必须top 1配order by，且不可含变量赋值或存储过程。

如何在sql server中利用apply子句改写复杂的嵌套子查询

SQL Server中APPLY比相关子查询更可控

直接用 CROSS APPLY 或 OUTER APPLY 替换相关子查询，能避免优化器对嵌套循环的误判，尤其当子查询含聚合、TOP、窗口函数或需逐行计算时。相关子查询在 SQL Server 中默认触发嵌套循环（Nested Loop），外层每行都重跑一次子查询；而 APPLY 会把右侧逻辑“物化为表”，让优化器有机会选择哈希连接或排序合并。

右侧子查询必须返回表结构（哪怕只有一列），不能是标量值；若原逻辑是 (SELECT name FROM users WHERE id = o.user_id)，需改写为 (SELECT TOP 1 name FROM users WHERE id = o.user_id) 并确保有索引
CROSS APPLY 等效于带条件的 INNER JOIN：左表某行若在右子查询中无结果，整行被过滤掉
OUTER APPLY 等效于 LEFT JOIN：右子查询无结果时，对应字段为 NULL，左表行保留
右侧子查询中禁止引用外部查询的多个表（如 o.user_id AND c.status 同时出现），否则报错 “The multi-part identifier could not be bound”

把标量子查询转成OUTER APPLY + TOP 1

标量子查询（如 SELECT ..., (SELECT MAX(date) FROM logs l WHERE l.order_id = o.id) AS last_log）在 SQL Server 中容易因 NULL 或多行报错，且无法下推谓词。改用 OUTER APPLY 更稳定，也便于加索引和调试。

必须加 TOP 1（即使逻辑上唯一），否则报错 “subquery used as an expression returned more than one value”
ORDER BY 要显式写清楚，否则 TOP 1 结果不可控；例如查最新登录时间，得写 ORDER BY login_time DESC
关联字段（如 l.order_id = o.id）必须放在子查询的 WHERE 中，不能靠 JOIN 条件隐含

示例改写：

SELECT o.order_id, last_log.date
FROM orders o
OUTER APPLY (
    SELECT TOP 1 login_time AS date
    FROM user_logins l
    WHERE l.order_id = o.id
    ORDER BY login_time DESC
) last_log;

注意：user_logins(order_id, login_time) 上应建复合索引，否则每次 APPLY 都全表扫描

用CROSS APPLY展开一对多聚合，避免GROUP BY上拉

当主查询需要对每个订单统计不同状态的子项数量（如 shipped_count、pending_count），若用多个相关子查询，会重复扫描子表；若用 GROUP BY + CASE WHEN，则需先聚合再 JOIN，可能丢失零值行。用 CROSS APPLY 可一次性展开多维统计，且保持主表行数不变。

右侧子查询用 VALUES 构造虚拟行，或用 SELECT ... GROUP BY 返回单行多列
不要在 APPLY 子查询里用 GROUP BY orders.id —— 外层 orders 已提供上下文，只需按业务维度分组（如 status）

示例：统计每订单各状态子项数

SELECT o.order_id, s.shipped, s.pending, s.cancelled
FROM orders o
CROSS APPLY (
    SELECT 
        COUNT(CASE WHEN i.status = 'shipped' THEN 1 END) AS shipped,
        COUNT(CASE WHEN i.status = 'pending' THEN 1 END) AS pending,
        COUNT(CASE WHEN i.status = 'cancelled' THEN 1 END) AS cancelled
    FROM order_items i
    WHERE i.order_id = o.order_id
) s;

这个写法比在主查询里写三个独立子查询快得多，也比先 GROUP BY order_id 再 LEFT JOIN 更节省内存

APPLY右侧不能有ORDER BY除非配TOP，也不能用变量赋值

这是最容易踩的语法坑。SQL Server 规定：APPLY 的右侧子查询若含 ORDER BY，必须搭配 TOP（或 OFFSET/FETCH），否则报错 “ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified”。