[T-SQL Querying]Chapter9~Apply

最新推荐文章于 2011-03-22 07:26:00 发布

weixin_34392906

最新推荐文章于 2011-03-22 07:26:00 发布

阅读量74

点赞数

原文链接：http://www.cnblogs.com/lizhe88/archive/2010/12/07/1899289.html

版权

Apply将左边表格看做基表，搜索右边表格。而Join则是等同的看待两表。

主要应用：搜索符合条件集合的前N个数据。

例子1：供应商各类别最便宜的N件商品。

IF OBJECT_ID('dbo.fn_top_products') IS NOT NULL
  DROP FUNCTION dbo.fn_top_products;
GO
CREATE FUNCTION dbo.fn_top_products
  (@supid AS INT, @catid INT, @n AS INT)
  RETURNS TABLE
AS
RETURN
  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, UnitPrice
FROM dbo.Suppliers AS S
  CROSS APPLY dbo.fn_top_products(S.SupplierID, 1, 2) AS P;

例子2： 各雇员最近N次订单详情。

SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate
FROM 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 PrvReqDate
FROM 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 Prv
ORDER BY Cur.EmployeeID, Cur.OrderDate, Cur.OrderID

转载于:https://www.cnblogs.com/lizhe88/archive/2010/12/07/1899289.html