一、什麼是Crossapply
Crossapply這個關鍵字在SQL Server中很常見，尤其在處理複雜查詢時。它是一種將兩個表格數據合併的操作，可以將一個表格中的每一行作為參數，傳遞到另一個表格中進行處理。這種操作通常用於嵌套查詢中，能夠有效的提高 SQL Server 的查詢性能。
Crossapply是一種關係型資料庫的查詢技術，能夠以一種高效的方式將兩張數據表合併，可以對多個表進行聯接，非常適合於在數據倉庫、BI和 OLAP等領域進行數據處理。它是SQL Server 2005及以後版本引入的，同時也是比較新的一種查詢技術。
二、Crossapply的語法
SELECT * FROM Table1 CROSS APPLY Table2
上面的示例中，Table1和Table2為兩張表格。使用Crossapply關鍵字後，會將Table1表中的每一行數據傳遞到Table2表中進行處理。因此， Crossapply的語法包含了兩個部分：第一部分為主查詢（Table1），第二個部分為子查詢（Table2）。
在SQL Server中，Crossapply常與外連接（Outer Join）、內聯接（Inner Join）以及A、B兩個表的聯接操作配合使用。具體語法結構如下（其中table1與table2為表格名）：
--內聯接的Crossapply使用方法 SELECT * FROM table1 CROSS APPLY table2 WHERE table1.id=table2.id --左外聯接的Crossapply使用方法 SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.id=table2.id CROSS APPLY table3 WHERE … --右外連接的Crossapply使用方法 SELECT * FROM table1 RIGHT OUTER JOIN table2 ON table1.id=table2.id CROSS APPLY table3 WHERE …
三、Crossapply的具體用法
1. 列與行數據處理
Crossapply關鍵字可以用於將一列數據轉換為行數據，或者將一行數據轉換為列數據。這種轉換是非常適合於 OLAP、數據倉庫和 BI等場景的數據處理需求。
例如，在一個庫存管理系統中，需要獲取不同商品的銷售統計信息。如果SQL語句如下：
SELECT * FROM Sales s WHERE s.SalesDate BETWEEN @FromDate AND @ToDate ORDER BY s.ProductId
這將返回一些類似下面這樣的結果：
ProductID SalesDate SalesAmount ---------- ----------------------- ------------ 100 2021-01-01 12:00:01.827 $350 100 2021-01-02 15:32:02.413 $450 101 2021-01-01 20:12:01.963 $120 …
現在需要按照每個商品ID進行統計，可以使用Crossapply來實現這個需求：
SELECT S.ProductId, C.CategoryName, Sales
FROM
(
SELECT DISTINCT ProductId
FROM Sales
) AS P
CROSS APPLY
(
SELECT COUNT(*) AS Sales
FROM Sales
WHERE Sales.ProductId=P.ProductId
AND Sales.SalesDate BETWEEN @FromDate AND @ToDate
) AS S
JOIN Products AS Pr ON Pr.ProductId = S.ProductId
JOIN Categories AS C ON Pr.CategoryId = C.CategoryId
ORDER BY Sales DESC, P.ProductId
上述代碼中，先使用DISTINCT獲取所有的Product ID，然後使用Crossapply對每個產品ID進行統計計算。
2. 橫向Pivot錶轉換
在 SQL Server 中，Crossapply常常用於對橫向表的轉換。例如，將Sales表從以產品ID為行轉換成以日期為行，以產品名稱為列的表格：
-- 定義一個基本的交易數據表
CREATE TABLE Sales
(
TranDate DATE, -- 交易日
ProductId INT, -- 產品ID
Quantity INT, -- 數量
Amount DECIMAL(16, 2) -- 金額
)
INSERT INTO Sales
VALUES
('01/01/2021', 101, 12, 123.23),
('01/02/2021', 101, 10, 120.00),
('01/01/2021', 102, 24, 240.00),
('01/02/2021', 102, 15, 150.00),
('01/01/2021', 103, 8 , 80.00),
('01/02/2021', 103, 14, 140.00)
下面使用Crossapply將橫向表格轉換為豎向表格：
SELECT
SalesDate,
[101] AS A,
[102] AS B,
[103] AS C
FROM
(
SELECT SalesDate, ProductId, Quantity
FROM Sales
) AS P
PIVOT
(
SUM(Quantity)
FOR ProductId IN ([101], [102], [103])
) AS S
CROSS APPLY
(
SELECT CategoryName
FROM Categories
WHERE S.ProductId = Categories.CategoryID
)AS C
ORDER BY SalesDate
上述代碼中，使用SELECT SalesDate,ProductId, Quantity FROM Sales將橫向表格轉換為豎向表格，然後使用Crossapply將不同ProductId對應的CategoryName合併到一行中輸出。
3. 子查詢優化
SQL查詢中的子查詢通常會影響查詢性能。 Subquery可以通過Crossapply轉化為 JOIN，從而提高查詢速度。
例如，下面的查詢用於查詢訂單信息：
SELECT *
FROM Orders AS O
WHERE EXISTS
(
SELECT TOP 1 *
FROM OrderDetails AS OD
WHERE O.OrderID=OD.OrderID
AND OrderDetails.ProductID=100
)
上述代碼中，子查詢WHERE語句中的EXISTS關鍵字用於搜索包含某個商品編號（100）的訂單編號。這個查詢其實是需要涉及到兩個表格，將其轉換為聯接查詢的形式可以優化查詢性能：
SELECT DISTINCT O.OrderID, O.OrderDate
FROM Orders AS O
CROSS APPLY
(
SELECT TOP 1 *
FROM OrderDetails AS OD
WHERE O.OrderID=OD.OrderID
AND OD.ProductID = 100
)AS OD1
ORDER BY O.OrderID
上述代碼中，OrderDetails表中僅選取了與Orders表有關聯的一行數據，Crossapply將子查詢轉化為聯接查詢，從而提升查詢性能。
四、Crossapply的優缺點
1. Crossapply的優點
Crossapply可以根據實際業務需求，將兩個表格進行聯接，並根據查詢條件進行篩選和加工。Crossapply的優點主要有：
（1）有效優化查詢性能。Crossapply能夠像內聯接那樣，只返回符合條件的記錄。在某些情況下，Crossapply要比子查詢的性能更好。
（2）可以進行列變行、行變列等數據轉換。例如，將橫向錶轉換成豎向表，是 Crossapply的常見用法。
（3）支持對複雜查詢進行優化。 Crossapply可以將多個子查詢聯接起來，進行一次性查詢，從而減少查詢時間和網路傳輸量。
2. Crossapply的缺點
與任何技術一樣，Crossapply也有其缺點。主要有：
（1）Crossapply需要通讀SQL語句，適應不同的查詢需求，並不是所有的查詢都適合使用Crossapply。
（2）該技術還是比較新的技術，有一定的學習成本。使用Crossapply需要了解SQL Server的查詢技巧，掌握複雜查詢的技巧。
（3） Crossapply只適用於SQL Server資料庫；其他資料庫不一定支持Crossapply。
五、總結
本文對Crossapply進行了全面的介紹，包括其語法、應用場景以及優缺點等方面。 Crossapply常用於處理複雜查詢，包括數據轉換、優化子查詢、聯接查詢等功能。 Crossapply的優點包括優化查詢性能、支持數據轉換，但是也存在一定的缺點。
總之， Crossapply是SQL Server中非常實用的查詢技巧，對於複雜查詢有很好的優化效果。在實際運用時，根據業務需求選擇合適的數據處理方式，既能提升查詢效率，又能確保數據準確性。
原創文章，作者：小藍，如若轉載，請註明出處：https://www.506064.com/zh-tw/n/150665.html
微信掃一掃 
支付寶掃一掃 