[T-SQL Querying]Chapter9~Apply

Apply將左邊表格看做基表，搜尋右邊表格。而Join則是等同的看待兩表。

主要應用：搜尋符合條件集合的前N個資料。

例子1：供應商各類別最便宜的N件商品。

　

IF OBJECT_ID('dbo.fn_top_products') IS NOT NULL  DROP FUNCTION dbo.fn_top_products;GOCREATE FUNCTION dbo.fn_top_products  (@supid AS INT, @catid INT, @n AS INT)  RETURNS TABLEASRETURN  SELECT TOP(@n) WITH TIES ProductID, ProductName, UnitPrice  FROM dbo.Products  WHERE SupplierID = @supid    AND CategoryID = @catid  ORDER BY UnitPrice DESC;

GO

SELECT S.SupplierID, CompanyName, ProductID, ProductName, UnitPriceFROM dbo.Suppliers AS S  CROSS APPLY dbo.fn_top_products(S.SupplierID, 1, 2) AS P;
 
例子2： 各僱員最近N次訂單詳情。
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDateFROM dbo.Employees AS E  CROSS APPLY    (SELECT TOP(3) OrderID, CustomerID, OrderDate, RequiredDate     FROM dbo.Orders AS O     WHERE O.EmployeeID = E.EmployeeID     ORDER BY OrderDate DESC, OrderID DESC) AS A;
 
例子3： 每行顯示訂單現在以及上次的訂單詳情。(方便進行比較)
   

    

     

      
SELECT Cur.EmployeeID,  Cur.OrderID AS CurOrderID, Prv.OrderID AS PrvOrderID,  Cur.OrderDate AS CurOrderDate, Prv.OrderDate AS PrvOrderDate,  Cur.RequiredDate AS CurReqDate, Prv.RequiredDate AS PrvReqDateFROM dbo.Orders AS Cur  OUTER APPLY    (SELECT TOP(1) OrderID, OrderDate, RequiredDate     FROM dbo.Orders AS O     WHERE O.EmployeeID = Cur.EmployeeID       AND (O.OrderDate < Cur.OrderDate            OR (O.OrderDate = Cur.OrderDate               AND O.OrderID < Cur.OrderID))     ORDER BY OrderDate DESC, OrderID DESC) AS PrvORDER BY Cur.EmployeeID, Cur.OrderDate, Cur.OrderID