为每个客户返回4个最新订单

Question

我想为每个CustomerID返回4个最新的。到目前为止，我有：

使用Northwind_2012

SELECT CustomerID, OrderDate
FROM Orders
ORDER BY CustomerID;

从这里开始，我想将其限制为每个CustomerID的4个最新OrderDate。为了实现这一点，我该何去何从，因为我也尝试过使用PARTITION BY和ROW_NUMBER，但还没能实现。

客户ID订单日期ALFKI 2007-08-25 00:00:00.000ALFKI 2007-10-03 00:00:00.000ALFKI 2007-10-13 00:00:00.000ALFKI 2008-015 00:00:00.000ALFKI 2008-03-16 00:00:00.000ALFKI 2008-04-09 00:00:00.000澳大利亚时间2008-03-04 00:00:00.000澳大利亚时间2007-11-28 00:00:00.000澳大利亚时间2007-08-08 00:00:00.000阿纳特2006-09-18 00:00:00.000安东2006-11-27 00:00:00.000安东2007-04-15 00:00:00.000

Answer 1

您可以使用row_number()，使用子查询或CTE:来完成此操作

SELECT CustomerID, OrderDate
FROM (SELECT o.*,
             ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) as seqnum
      FROM Orders
     ) o
WHERE seqnum <= 4
ORDER BY CustomerID;

Answer 2

经过测试，工作：

（假设"OrderID"主键：

SELECT CustomerId, OrderDate
FROM Orders as ExtOrders
WHERE OrderId IN (
     SELECT TOP 4 OrderId
     FROM Orders
     WHERE CustomerId LIKE ExtOrders.CustomerId
     ORDER BY OrderDate DESC)
ORDER BY CustomerId; -- Might not work in SQLFiddle if CustomerId is text

SQLFiddle

Answer 3

Edit，我没有彻底阅读这个问题。试试这个：

Use Northwind_2012
WITH e
AS (
    SELECT ROW_NUMBER() OVER (
            PARTITION BY CustomerID ORDER BY OrderDate DESC
            ) AS rownum
        ,CustomerID
        ,OrderDate
    FROM Orders
    )
SELECT CustomerID
    ,OrderDate
FROM e
WHERE rownum <= 4

http://technet.microsoft.com/en-us/library/ms189461.aspx

SQLFiddle：http://sqlfiddle.com/#！6/afe40/11/0