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

[color=blue]1、每组中的TOP n问题[/color]
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

[color=green]-- 解决方案一: Solution 1 to the Most Recent Order for each Employee Problem[/color]
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);

[color=green]-- 解决方案二: Solution 1 to the n Most Recent Orders for each Employee Problem[/color]
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);

[color=green]-- 解决方案三: Solution 2 to the Most Recent Order for each Employee Problem[/color]
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;

[color=green]-- 解决方案四: Solution 2 to the n Most Recent Orders for each Employee Problem[/color]
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);

[color=green]-- 解决方案五: Solution 3 to the n Most Recent Orders for each Employee Problem[/color]
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

[color=green]-- 解决方案六: Solution 4 to the n Most Recent Orders for each Employee Problem[/color]
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;


[color=blue]2、匹配当前值和前一个值[/color]
[color=green]-- 解决方案一: Query Solution 1 Matching Current and Previous Occurrences[/color]
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;

[color=green]-- 解决方案二: Query Solution 2 Matching Current and Previous Occurrences[/color]
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;

[color=green]-- 解决方案三: Query Solution 3 Matching Current and Previous Occurrences[/color]
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;

[color=green]-- 解决方案四: Query Solution 4 Matching Current and Previous Occurrences[/color]
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