T-SQL查询学习笔记——使用TOP和APPLY解决常见问题

1、每组中的TOP n问题
CREATE UNIQUE INDEX idx_eid_od_oid_i_cid_rd
  ON dbo.Orders(EmployeeID, OrderDate, OrderID)
     INCLUDE(CustomerID, RequiredDate);

CREATE UNIQUE INDEX idx_oid_qtyd_pid
  ON dbo.[Order Details](OrderID, Quantity DESC, ProductID);
GO

-- 解决方案一: Solution 1 to the Most Recent Order for each Employee Problem
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate
FROM dbo.Orders AS O1
WHERE OrderID =
  (SELECT TOP(1) OrderID
   FROM dbo.Orders AS O2
   WHERE O2.EmployeeID = O1.EmployeeID
   ORDER BY OrderDate DESC, OrderID DESC);

-- 解决方案二: Solution 1 to the n Most Recent Orders for each Employee Problem
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate
FROM dbo.Orders AS O1
WHERE OrderID IN
  (SELECT TOP(3) OrderID
   FROM dbo.Orders AS O2
   WHERE O2.EmployeeID = O1.EmployeeID
   ORDER BY OrderDate DESC, OrderID DESC);

-- 解决方案三: Solution 2 to the Most Recent Order for each Employee Problem
SELECT O.OrderID, CustomerID, O.EmployeeID, OrderDate, RequiredDate
FROM (SELECT EmployeeID,
        (SELECT TOP(1) OrderID
         FROM dbo.Orders AS O2
         WHERE O2.EmployeeID = E.EmployeeID
         ORDER BY OrderDate DESC, OrderID DESC) AS TopOrder
      FROM dbo.Employees AS E) AS EO
  JOIN dbo.Orders AS O
    ON O.OrderID = EO.TopOrder;

-- 解决方案四: Solution 2 to the n Most Recent Orders for each Employee Problem
SELECT OrderID, CustomerID, E.EmployeeID, OrderDate, RequiredDate
FROM dbo.Employees AS E
   JOIN dbo.Orders AS O1
     ON OrderID IN
       (SELECT TOP(3) OrderID
        FROM dbo.Orders AS O2
        WHERE O2.EmployeeID = E.EmployeeID
        ORDER BY OrderDate DESC, OrderID DESC);

-- 解决方案五: Solution 3 to the n Most Recent Orders for each Employee Problem
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;
GO

-- Creade optimal index for next solution
CREATE UNIQUE INDEX idx_eid_odD_oidD_i_cid_rd
  ON dbo.Orders(EmployeeID, OrderDate DESC, OrderID DESC)
     INCLUDE(CustomerID, RequiredDate);
GO

-- 解决方案六: Solution 4 to the n Most Recent Orders for each Employee Problem
SELECT OrderID, CustomerID, OrderDate, RequiredDate
FROM (SELECT OrderID, CustomerID, OrderDate, RequiredDate,
        ROW_NUMBER() OVER(PARTITION BY EmployeeID
                          ORDER BY OrderDate DESC, OrderID DESC) AS RowNum
      FROM dbo.Orders) AS D
WHERE RowNum <= 3;


2、匹配当前值和前一个值
-- 解决方案一: Query Solution 1 Matching Current and Previous Occurrences
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
  LEFT OUTER JOIN dbo.Orders AS Prv
    ON Prv.OrderID =
       (SELECT TOP(1) OrderID
        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)
ORDER BY Cur.EmployeeID, Cur.OrderDate, Cur.OrderID;

-- 解决方案二: Query Solution 2 Matching Current and Previous Occurrences
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 (SELECT EmployeeID, OrderID, OrderDate, RequiredDate,
        (SELECT TOP(1) OrderID
         FROM dbo.Orders AS O2
         WHERE O2.EmployeeID = O1.EmployeeID
           AND (O2.OrderDate < O1.OrderDate
                OR O2.OrderDate = O1.OrderDate
                   AND O2.OrderID < O1.OrderID)
         ORDER BY OrderDate DESC, OrderID DESC) AS PrvOrderID
      FROM dbo.Orders AS O1) AS Cur
  LEFT OUTER JOIN dbo.Orders AS Prv
    ON Cur.PrvOrderID = Prv.OrderID
ORDER BY Cur.EmployeeID, Cur.OrderDate, Cur.OrderID;

-- 解决方案三: Query Solution 3 Matching Current and Previous Occurrences
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;

-- 解决方案四: Query Solution 4 Matching Current and Previous Occurrences
WITH OrdersRN AS
(
  SELECT EmployeeID, OrderID, OrderDate, RequiredDate,
    ROW_NUMBER() OVER(PARTITION BY EmployeeID
                      ORDER BY OrderDate, OrderID) AS rn
  FROM dbo.Orders
)
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 OrdersRN AS Cur
  LEFT OUTER JOIN OrdersRN AS Prv
    ON Cur.EmployeeID = Prv.EmployeeID
    AND Cur.rn = Prv.rn + 1
ORDER BY Cur.EmployeeID, Cur.OrderDate, Cur.OrderID;
GO