sql-server - SQL Server 分组

Question

我有如下三个表:

DECLARE @Orders table ( OrderId int NOT NULL,
                        CustomerId int NOT NULL,
                        OrderDate datetime NOT NULL,
                        PRIMARY KEY ( OrderId ) );

    DECLARE @Printers table ( PrinterId int NOT NULL,
                          Printer varchar(50) NOT NULL,
                          Size varchar(6) NOT NULL,
                          PRIMARY KEY ( PrinterId ) );

    DECLARE @OrderBillPrints table ( OrderId int NOT NULL,
                                 PrinterId int NOT NULL,
                                 PrintDate datetime NOT NULL,
                                 PRIMARY KEY ( OrderId, PrinterId, PrintDate )       );`

和示例数据:

 INSERT INTO @Orders ( OrderId, CustomerId, OrderDate )
    VALUES ( 1, 1, '20150105' ),
       ( 2, 5, '20150102' ),
       ( 3, 1, '20150110' ),
       ( 4, 3, '20150101' ),
       ( 5, 8, '20150215' ),
       ( 6, 5, '20150305' ),
       ( 7, 2, '20150228' ),
       ( 8, 1, '20150302' ),
       ( 9, 6, '20150301' )

   INSERT INTO @Printers ( PrinterId, Printer, Size )
   VALUES ( 1, 'Wild Bills Inkshop', 'Large' ),
       ( 2, 'Sammies Samples', 'Medium' ),
       ( 3, 'Large Font Press', 'Large' ),
       ( 4, '5_NE_HP_1400', 'Small' )

    INSERT INTO @OrderBillPrints ( OrderId, PrinterId, PrintDate )
    VALUES ( 1, 2, '20150101' ),
       ( 4, 3, '20150102' ),
       ( 2, 4, '20150102' ),
       ( 1, 1, '20150102' ),
       ( 5, 3, '20150102' ),
       ( 1, 3, '20150103' ),
       ( 1, 2, '20150103' ),
       ( 4, 2, '20150104' ),
       ( 3, 1, '20150112' ),
       ( 7, 4, '20150301' ),
       ( 9, 4, '20150301' ),
       ( 7, 4, '20150302' ),
       ( 7, 1, '20150302' ),
       ( 9, 2, '20150303' ),
       ( 9, 4, '20150303' ),
       ( 9, 2, '20150304' )

我需要查询以查看每个订单的最新打印运行信息，并且我为每个订单返回的一条记录除外，其中包含以下信息:

OrderId
OrderDate LargestPrinterFromLastRun
LastPrintRunDate
LargestPrinterFromLastRunSize

以下是我的查询:

SELECT T.OrderId , MAX(T.LargestPrinterFromLastRun),  T.OrderDate ,
    MAX(T.LastPrintRunDate) as LastPrintRunDate, 
   MIN(LargestPrinterFromLastRunSize) AS 
   LargestPrinterFromLastRunSize FROM
  (
  SELECT 
  O.OrderId, MAX(o.OrderDate) AS OrderDate, 
  CASE 
    WHEN P.Printer IS NULL THEN '*N/A*'
    ELSE P.Printer
    END AS LargestPrinterFromLastRun, OBP.PrintDate AS LastPrintRunDate, 
    P.Size AS LargestPrinterFromLastRunSize 
   FROM @Orders O
    LEFT JOIN @OrderBillPrints OBP ON O.OrderId = OBP.OrderId
   LEFT JOIN @Printers P ON P.PrinterId = OBP.PrinterId
   GROUP BY O.OrderId, P.Size, P.Printer, OBP.PrintDate) T 
   GROUP BY T.OrderId, T.OrderDate

   ORDER BY T.OrderId ASC 

输出应该是:

OrderId OrderDate               LargestPrinterFromLastRun   LastPrintRunDate        LargestPrinterFromLastRunSize
1       2015-01-05 00:00:00.000 Large Font Press            2015-01-03 00:00:00.000 Large
2       2015-01-02 00:00:00.000 5_NE_HP_1400                2015-01-02 00:00:00.000 Small
3       2015-01-10 00:00:00.000 Wild Bills Inkshop          2015-01-12 00:00:00.000 Large
4       2015-01-01 00:00:00.000 Sammies Samples             2015-01-04 00:00:00.000 Medium
5       2015-02-15 00:00:00.000 Large Font Press            2015-01-02 00:00:00.000 Large
6       2015-03-05 00:00:00.000 *N/A*                       NULL                    NULL
7       2015-02-28 00:00:00.000 Wild Bills Inkshop          2015-03-02 00:00:00.000 Large
8       2015-03-02 00:00:00.000 *N/A*                       NULL                    NULL
9       2015-03-01 00:00:00.000 Sammies Samples             2015-03-04 00:00:00.000 Medium

如有任何帮助，我们将不胜感激。

这是我的结果不匹配

 OrderId        LargestPrinterFromLastRun   OrderDate               LastPrintRunDate        LargestPrinterFromLastRunSize
1           Wild Bills Inkshop          2015-01-05 00:00:00.000 2015-01-03 00:00:00.000 Large
2           5_NE_HP_1400                2015-01-02 00:00:00.000 2015-01-02 00:00:00.000 Small
3           Wild Bills Inkshop          2015-01-10 00:00:00.000 2015-01-12 00:00:00.000 Large
4           Sammies Samples             2015-01-01 00:00:00.000 2015-01-04 00:00:00.000 Large
5           Large Font Press            2015-02-15 00:00:00.000 2015-01-02 00:00:00.000 Large
6           *N/A*                       2015-03-05 00:00:00.000 NULL                    NULL
7           Wild Bills Inkshop          2015-02-28 00:00:00.000 2015-03-02 00:00:00.000 Large
8           *N/A*                       2015-03-02 00:00:00.000 NULL                    NULL
9           Sammies Samples             2015-03-01 00:00:00.000 2015-03-04 00:00:00.000 Medium

Answer 1

试试这个，它与您使用 Rank() 时的结果相匹配

SELECT OrderId,
       OrderDate,
       ISNULL(Printer,'*N/A* ') AS LargestPrinterFromLastRun,
       PrintDate AS LastPrintRunDate,
       SIZE AS LargestPrinterFromLastRunSize
FROM
  (SELECT A.OrderId,Printer, PrintDate ,SIZE,
     (SELECT max(OrderDate)
      FROM @Orders
      WHERE OrderDate =A.OrderDate) AS [OrderDate],
      RANK () OVER(PARTITION BY A.OrderId ORDER BY PrintDate DESC ,Printer ASC) Ranks
   FROM @Orders A
   LEFT JOIN @OrderBillPrints B ON A.OrderId=B.OrderId
   LEFT JOIN @Printers C ON B.PrinterId=C.PrinterId) Ms
WHERE Ranks=1
GROUP BY OrderId,
         [OrderDate],
         Printer,
         PrintDate,
         SIZE