APPLY 允許對左側表表達式的**每一行**調用右側**表值函數**或**子查詢**,右側可引用左側列。結果為左側列 + 右側返回列的拼接。
測試表:
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);
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 **無行**(被過濾)。
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 壓力 |
| 相關子查詢 + TOP | SELECT * 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 專有、不可移植 |