您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> SQL Server 2005

SQL Server 2005 APPLY 運算子完整教學：CROSS APPLY 與 OUTER APPLY 實戰對比

編輯：SQL Server 2005

SQL Server 2005 引入 APPLY 運算子，允許將表值函數 (TVF) 套用至外部查詢的每一列，解決「每列呼叫函數並展開結果」的問題。本文完整示範 CROSS APPLY (內部聯接語意) 與 OUTER APPLY (左外部聯接語意) 的差異、建立拆解逗號字串的 TVF、與 SQL 2000 游標寫法對比。

核心概念

左輸入：外部表表達式 (如資料表、子查詢)
右輸入：表值函數 (可引用左輸入的欄位作參數)
運作方式：對左輸入每一列，執行右輸入 TVF，將結果集「橫向展開」連接
兩種模式：
- CROSS APPLY：僅回傳 TVF 有結果的列 (如 INNER JOIN)
- OUTER APPLY：回傳所有左輸入列，TVF 無結果時右側欄位為 NULL (如 LEFT JOIN)

建立測試資料與 TVF

CREATE TABLE MyData (
    ids INT IDENTITY PRIMARY KEY,
    Data NVARCHAR(1000)
);

INSERT INTO MyData VALUES (''), ('a,b,c'), ('q'), ('i,p');

-- TVF：依逗號拆解字串，每段產生一列 (id, value)
CREATE FUNCTION dbo.fn_MyData(@data NVARCHAR(1000))
RETURNS @tem TABLE (id INT, value NVARCHAR(100))
AS
BEGIN
    SET @data = ISNULL(@data, '');
    IF LEN(@data) = 0 RETURN;

    DECLARE @id INT = 1, @end INT = CHARINDEX(',', @data);
    WHILE @end > 0
    BEGIN
        INSERT @tem SELECT @id, LEFT(@data, @end - 1);
        SET @id += 1;
        SET @data = RIGHT(@data, LEN(@data) - @end);
        SET @end = CHARINDEX(',', @data);
    END
    IF LEN(@data) > 0 INSERT @tem SELECT @id, @data;
    RETURN;
END

CROSS APPLY 範例

SELECT m.ids, f.*
FROM MyData m CROSS APPLY dbo.fn_MyData(m.Data) f;

結果

ids id value
2   1  a
2   2  b
2   3  c
3   1  q
4   1  i
4   2  p

ids=1 (空字串) TVF 回傳空集 → 被過濾掉

OUTER APPLY 範例

SELECT m.ids, f.*
FROM MyData m OUTER APPLY dbo.fn_MyData(m.Data) f;

結果

ids id value
1   NULL NULL  -- 空字串列保留，右側為 NULL
2   1  a
2   2  b
2   3  c
3   1  q
4   1  i
4   2  p

SQL 2000 替代寫法：游標迴圈 (繁瑣、效能差)

DECLARE @ids INT, @data NVARCHAR(200);
DECLARE @tem TABLE (ids INT, id INT, value NVARCHAR(100));

DECLARE cur CURSOR FOR SELECT ids, Data FROM MyData;
OPEN cur;
FETCH NEXT FROM cur INTO @ids, @data;
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT @tem SELECT @ids, id, value FROM dbo.fn_MyData(@data);
    FETCH NEXT FROM cur INTO @ids, @data;
END
CLOSE cur; DEALLOCATE cur;

SELECT * FROM @tem;

進階應用場景

1. 取得每個客戶最新的一筆訂單

SELECT c.*, o.*
FROM Customers c
CROSS APPLY (
    SELECT TOP 1 * FROM Orders o 
    WHERE o.CustomerID = c.CustomerID 
    ORDER BY OrderDate DESC
) o;

2. 動態拆解 JSON/XML/分隔字串 (搭配 OPENJSON、XML.nodes、STRING_SPLIT)

-- SQL 2016+ STRING_SPLIT
SELECT c.*, s.value
FROM Customers c
CROSS APPLY STRING_SPLIT(c.Tags, ',') s;

-- XML 欄位拆解
SELECT c.*, x.c.value('.', 'NVARCHAR(100)')
FROM Customers c
CROSS APPLY c.Preferences.nodes('/prefs/item') x(c);

3. 避免重複計算：將複雜運算封裝在 TVF，APPLY 呼叫

CREATE FUNCTION dbo.CalcCustomerMetrics(@CustID INT)
RETURNS TABLE
AS RETURN (
    SELECT 
        @CustID AS CustomerID,
        COUNT(*) AS OrderCount,
        SUM(Amount) AS TotalAmount,
        AVG(Amount) AS AvgAmount
    FROM Orders WHERE CustomerID = @CustID
);

SELECT c.*, m.*
FROM Customers c
CROSS APPLY dbo.CalcCustomerMetrics(c.CustomerID) m;