在TSQL中使用GROUP BY子句 [英] Variant use of the GROUP BY clause in TSQL

问题描述

假设下面的模式和示例数据（SQL Server 2008）：

pre $ $ $ $ $ $ OriginateObject
----- -----------------------------------------
ID
1
2
3

ValueSet
-------------------------- --------------------
ID OriginatingObjectID DateStamp
1 1 2009-05-21 10:41:43
2 1 2009 -05-22 12:11:51
3 1 2009-05-22 12:13:25
4 2 2009-05-21 10:42:40
5 2 2009-05 -20 02:21:34
6 1 2009-05-21 23:41:43
7 3 2009-05-26 14:56:01

价值
----------------------------------------------
ID ValueSetID值
1 1 28
etc（每个相关ValueSet的一组行）

我需要为每个OriginatingObject获取最近的ValueSet记录的ID。不要认为记录的ID越高，越新。

我不确定如何正确使用GROUP BY以确保设置分组在一起以形成每个聚合行的结果包括该分组具有最高DateStamp值的行的ID。我需要使用子查询还是有更好的方法？

解决方案

您可以使用相关子查询或使用IN有多个列和一个GROUP BY。

请注意，简单的GROUP-BY只能将您带到OriginatingID和Timestamps列表。为了拉取相关的ValueSet ID，最干净的解决方案是使用子查询。

使用GROUP BY的多列IN（可能更快）：

  SELECT O.ID，V.ID 
 FROM Originating AS O，ValueSet AS V 
 WHERE O.ID = V.OriginatingID 
 AND 
（V.OriginatingID，V. DateStamp）IN 
（
 SELECT OriginatingID，Max（DateStamp）
 FROM ValueSet 
 GROUP BY OriginatingID 
）
  

相关子查询：

  SELECT O.ID，V. ID 
 FROM Originating AS O，ValueSet AS V 
 WHERE O.ID = V.OriginatingID 
 AND 
 V.DateStamp = 
（
 SELECT Max（ DateStamp）
 FROM ValueSet V2 
 WHERE V2.OriginatingID = O.ID 
）
  

Imagine the following schema and sample data (SQL Server 2008):

OriginatingObject
----------------------------------------------
ID
1
2
3

ValueSet
----------------------------------------------
ID   OriginatingObjectID   DateStamp
1    1                     2009-05-21 10:41:43
2    1                     2009-05-22 12:11:51
3    1                     2009-05-22 12:13:25
4    2                     2009-05-21 10:42:40
5    2                     2009-05-20 02:21:34
6    1                     2009-05-21 23:41:43
7    3                     2009-05-26 14:56:01

Value
----------------------------------------------
ID   ValueSetID   Value
1    1            28
etc (a set of rows for each related ValueSet)

I need to obtain the ID of the most recent ValueSet record for each OriginatingObject. Do not assume that the higher the ID of a record, the more recent it is.

I am not sure how to use GROUP BY properly in order to make sure the set of results grouped together to form each aggregate row includes the ID of the row with the highest DateStamp value for that grouping. Do I need to use a subquery or is there a better way?

解决方案

You can do it with a correlated subquery or using IN with multiple columns and a GROUP-BY.

Please note, simple GROUP-BY can only bring you to the list of OriginatingIDs and Timestamps. In order to pull the relevant ValueSet IDs, the cleanest solution is use a subquery.

Multiple-column IN with GROUP-BY (probably faster):

SELECT O.ID, V.ID
FROM Originating AS O, ValueSet AS V
WHERE O.ID = V.OriginatingID
AND
(V.OriginatingID, V.DateStamp) IN
(
    SELECT OriginatingID, Max(DateStamp)
    FROM ValueSet
    GROUP BY OriginatingID
)

Correlated Subquery:

SELECT O.ID, V.ID
FROM Originating AS O, ValueSet AS V
WHERE O.ID = V.OriginatingID
AND
V.DateStamp =
(
    SELECT Max(DateStamp)
    FROM ValueSet V2
    WHERE V2.OriginatingID = O.ID
)

这篇关于在TSQL中使用GROUP BY子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！