sql server语言 - 从多个表的INNER join中返回GROUP By查询的TOP n行

Question

我在你的网站上尝试了几种解决方案，但没有一种完全符合我的需要。在SQL Server中，我需要使用GROUP BY查询进行多表连接，并仅从每个组返回TOP 1行。下面是返回所需记录的基本代码，但将返回符合条件的所有记录:

SELECT MIN(Maintenance_Log.Log_StartDate) As MaintDate
     , Manufacturer.Manufacturer_Name
     , GL_Inventory.Inventory_Model
     , GL_Inventory.Inventory_SerialNr
     , Maintenance_Log.Event_ID
     , Maintenance_Log.Inventory_ID 
FROM Maintenance_Log 
INNER JOIN GL_Inventory ON Maintenance_Log.Inventory_ID = GL_Inventory.Inventory_ID 
INNER JOIN Manufacturer ON GL_Inventory.Manufacturer_ID = Manufacturer.Manufacturer_ID  
WHERE Maintenance_Log.Owner_ID = @OwnerID 
  AND Maintenance_Log.Log_CompletedDate Is Null 
GROUP BY Maintenance_Log.Inventory_ID
       , Maintenance_Log.Log_StartDate
       , Manufacturer.Manufacturer_Name
       , GL_Inventory.Inventory_Model
       , GL_Inventory.Inventory_SerialNr
       , Maintenance_Log.Event_ID 

返回的结果如下:

2016-06-30 09:00:00.000 GLOCK   G19 Gen 4   ABDE1234    2   6
2016-07-28 09:00:00.000 GLOCK   G19 Gen 4   ABDE1234    3   6
2016-08-25 09:00:00.000 GLOCK   G19 Gen 4   ABDE1234    4   6
2016-09-29 09:00:00.000 GLOCK   G19 Gen 4   ABDE1234    5   6
2016-10-27 09:00:00.000 GLOCK   G19 Gen 4   ABDE1234    6   6
2016-11-24 09:00:00.000 GLOCK   G19 Gen 4   ABDE1234    7   6
2016-12-29 09:00:00.000 GLOCK   G19 Gen 4   ABDE1234    8   6
2016-07-01 09:00:00.000 S&W     642         WQ32De45    9   7
2016-08-05 09:00:00.000 S&W     642         WQ32De45    10  7
2016-09-02 09:00:00.000 S&W     642         WQ32De45    11  7
2016-10-07 09:00:00.000 S&W     642         WQ32De45    12  7
2016-11-04 09:00:00.000 S&W     642         WQ32De45    13  7
2016-12-02 09:00:00.000 S&W     642         WQ32De45    14  7

我想只返回TOP 1行从每组基于行的最后一个数字，这是Inventory_ID数字，而不是所有的行匹配的标准。

Answer 1

这是您的查询，使用表别名格式化和简化:

SELECT MIN(l.Log_StartDate) As MaintDate, m.Manufacturer_Name, 
       i.Inventory_Model, i.Inventory_SerialNr, l.Event_ID, l.Inventory_ID 
FROM Maintenance_Log l INNER JOIN
     GL_Inventory i
     ON l.Inventory_ID = i.Inventory_ID INNER JOIN
     Manufacturer m
     ON i.Manufacturer_ID = m.Manufacturer_ID)
WHERE l.Owner_ID = @OwnerID AND l.Log_CompletedDate Is Null 
GROUP BY l.Log_StartDate, m.Manufacturer_Name, i.Inventory_Model, 
         i.Inventory_SerialNr, l.Event_ID, l.Inventory_ID ;

首先，我注意到Log_StartDate既在GROUP BY中，也是MIN()的参数。所以，我想知道这是否会解决你的问题:

SELECT MIN(l.Log_StartDate) As MaintDate, m.Manufacturer_Name, 
       i.Inventory_Model, i.Inventory_SerialNr, l.Event_ID, l.Inventory_ID 
FROM Maintenance_Log l INNER JOIN
     GL_Inventory i
     ON l.Inventory_ID = i.Inventory_ID INNER JOIN
     Manufacturer m
     ON i.Manufacturer_ID = m.Manufacturer_ID)
WHERE l.Owner_ID = @OwnerID AND l.Log_CompletedDate Is Null 
GROUP BY m.Manufacturer_Name, i.Inventory_Model, 
         i.Inventory_SerialNr, l.Event_ID, l.Inventory_ID ;

如果您仍然需要最大的inventory_id的一行，那么您可以使用窗口函数:

WITH t as (
      SELECT MIN(l.Log_StartDate) As MaintDate, m.Manufacturer_Name, 
             i.Inventory_Model, i.Inventory_SerialNr, l.Event_ID, l.Inventory_ID ,
             ROW_NUMBER() OVER (PARTITION BY m.Manufacturer_Name,  i.Inventory_Model, i.Inventory_SerialNr, l.Event_ID
                                ORDER BY l.Inventory_ID  -- Do you want this ASC or DESC ?
                               ) as seqnum
      FROM Maintenance_Log l INNER JOIN
           GL_Inventory i
           ON l.Inventory_ID = i.Inventory_ID INNER JOIN
           Manufacturer m
           ON i.Manufacturer_ID = m.Manufacturer_ID)
      WHERE l.Owner_ID = @OwnerID AND l.Log_CompletedDate Is Null 
      GROUP BY m.Manufacturer_Name, i.Inventory_Model, 
               i.Inventory_SerialNr, l.Event_ID, l.Inventory_ID
     )
SELECT t.*
FROM t
WHERE seqnum = 1;

Answer 2

WITH X AS (
SELECT ML.Log_StartDate As MaintDate
     , M.M_Name
     , I.Inventory_Model
     , I.Inventory_SerialNr
     , ML.Event_ID
     , ML.Inventory_ID 
     , ROW_NUMBER() OVER (PARTITION BY ML.Inventory_ID 
                                    ORDER BY ML.Log_StartDate DESC)rn
FROM Maintenance_Log ML 
INNER JOIN GL_Inventory I ON ML.Inventory_ID = I.Inventory_ID 
INNER JOIN Manufacturer M ON I.M_ID = M.M_ID  
WHERE ML.Owner_ID = @OwnerID 
  AND ML.Log_CompletedDate Is Null 
)
SELECT MaintDate
      ,M_Name
      ,Inventory_Model
      ,Inventory_SerialNr
      ,Event_ID
      ,Inventory_ID
FROM X
WHERE RN = 1

Answer 3

通常在这种情况下(需要从子查询中获取连接的每一行的前1个结果)，CROSS APPLY就是您想要的。

INNER JOIN (select top 1…)将对连接的每一行使用相同的"top 1"行，而CROSS APPLY (select top 1…)将对连接的每一行计算表达式。

如果您需要它的行为像LEFT OUTER JOIN那样(不过滤表达式中没有结果的行)，请使用OUTER APPLY代替。

Answer 4

由于每一行的唯一性，查询执行了您所要求的操作。

根据您发布的数据(假设第一个日期的Maintenance_Log.Event_ID最小)，下面的查询可能是一个解决方案。

 SELECT MIN(Maintenance_Log.Log_StartDate) As MaintDate
     , Manufacturer.Manufacturer_Name
     , GL_Inventory.Inventory_Model
     , GL_Inventory.Inventory_SerialNr
     , MIN(Maintenance_Log.Event_ID)
     , Maintenance_Log.Inventory_ID 
FROM Maintenance_Log 
INNER JOIN GL_Inventory ON Maintenance_Log.Inventory_ID = GL_Inventory.Inventory_ID 
INNER JOIN Manufacturer ON GL_Inventory.Manufacturer_ID = Manufacturer.Manufacturer_ID  
WHERE Maintenance_Log.Owner_ID = @OwnerID 
  AND Maintenance_Log.Log_CompletedDate Is Null 
GROUP BY Maintenance_Log.Inventory_ID
       , Maintenance_Log.Log_StartDate
       , Manufacturer.Manufacturer_Name
       , GL_Inventory.Inventory_Model
       , GL_Inventory.Inventory_SerialNr
       --, Maintenance_Log.Event_ID