萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server APPLY 操作符詳解:CROSS APPLY vs OUTER APPLY、替代方案與性能對比

APPLY 基本語義

APPLY 允許對左側表表達式的**每一行**調用右側**表值函數**或**子查詢**,右側可引用左側列。結果為左側列 + 右側返回列的拼接。

  • CROSS APPLY:內連接語義,右側無行則左側行被過濾。
  • OUTER APPLY:左外連接語義,右側無行則用 NULL 填充右側列。

場景:每類取 Top N

測試表:

CREATE TABLE Dept (Id INT IDENTITY, Name VARCHAR(100));
CREATE TABLE Emp (DeptId INT, Score INT);
INSERT INTO Dept VALUES ('LeeWhoeeUniversity'),('LeeWhoee'),('DePaul');
INSERT INTO Emp VALUES (1,100),(1,90),(1,90),(1,80),(2,90),(2,82),(2,10);

CROSS APPLY 取每部門前 2 高分

SELECT d.Name, e.Score
FROM Dept d
CROSS APPLY (
    SELECT TOP 2 Score FROM Emp WHERE DeptId = d.Id ORDER BY Score DESC
) e;

結果:LeeWhoeeUniversity 兩行、LeeWhoee 兩行、DePaul **無行**(被過濾)。

OUTER APPLY 保留無員工部門

SELECT d.Name, e.Score
FROM Dept d
OUTER APPLY (
    SELECT TOP 2 Score FROM Emp WHERE DeptId = d.Id ORDER BY Score DESC
) e;

結果:DePaul 一行,Score 為 NULL。

替代方案對比

方案SQL優點缺點
ROW_NUMBER + 窗口函數SELECT * FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY DeptId ORDER BY Score DESC) RN FROM Emp) t WHERE RN <= 2標準 SQL、可移植、單掃描需物化中間結果,大數據集 TempDB 壓力
相關子查詢 + TOPSELECT * FROM Dept d JOIN Emp e ON e.DeptId=d.Id WHERE e.Id IN (SELECT TOP 2 Id FROM Emp WHERE DeptId=d.Id ORDER BY Score DESC)舊版本兼容需主鍵、計劃不穩、性能差
APPLY見上語義清晰、就地計算、優化器可展開T-SQL 專有、不可移植

優化器行為

  • APPLY 通常被**展開為等價 JOIN + 濾波** 計劃。
  • 若右側為**內聯表值函數 (iTVF)**,展開最徹底,性能最佳。
  • 若為**多語句表值函數 (mTVF)**,視為黑盒,每行調用開銷大,**慎用**。

適用場景清單

  1. 每組 Top N / 首末記錄
  2. 調用內聯表值函數傳參(如拆分字符串、獲取屬性表)
  3. 動態列構造(如 JSON 解析、XML 節點展開)
  4. 替代相關子查詢提升可讀性
copyright © 萬盛學電腦網 all rights reserved