当按两列分组时，获取最新记录SQL Server

问题描述：

我有一个跟踪版本历史的表。我只想获取最新版本以及表中每个id的日期。下面将给我每个id，版本组合的最新日期。我如何只选择最高记录？我可以将其存储在临时表中，然后使用连接来获取每个id，版本组合的最高记录。是否有更好的方法可以在单个步骤中完成此操作？当按两列分组时，获取最新记录SQL Server

的样本数据

id version  timestamp 
123 1.5   2015-03-28 08:21:04.563 
123 1.0   2015-03-21 12:58:24.730 
234 1.5   2016-10-15 23:08:09.550 
345 1.5   2016-05-10 15:18:09.707 
345 1.5   2016-09-02 21:30:00.657

预计输出

id version  timestamp 
123 1.5   2015-03-28 08:21:04.563 
234 1.5   2016-10-15 23:08:09.550 
345 1.5   2016-09-02 21:30:00.657

查询

select id,version,max(dt_create) 
from version_history (nolock) 
group by id,version 
order by id

我都试过，但我得到了同样的结果如上

select * from 
( 
    select id,version,dt_create,row_number() over (partition by id,version order by dt_create desc) as a 
    from version_history (nolock) 
) b 
where a=1 
order by id

答

第二个查询我的作品后，我从分区子句删除版本

select * from 
( 
    select id,version,dt_create,row_number() over (partition by id order by dt_create desc) as a 
    from version_history (nolock) 
) b 
where a=1 
order by id

答

您可以使用WITH TIES子句

例

Declare @YourTable Table ([id] int,[version] varchar(50),[timestamp] datetime) 
Insert Into @YourTable Values 
(123,1.5,'2015-03-28 08:21:04.563') 
,(123,1.0,'2015-03-21 12:58:24.730') 
,(234,1.5,'2016-10-15 23:08:09.550') 
,(345,1.5,'2016-05-10 15:18:09.707') 
,(345,1.5,'2016-09-02 21:30:00.657') 

Select Top 1 with ties * 
From @YourTable 
Order By Row_Number() over (Partition By ID Order By timestamp Desc)

id version timestamp 
123 1.5  2015-03-28 08:21:04.563 
234 1.5  2016-10-15 23:08:09.550 
345 1.5  2016-09-02 21:30:00.657

Thanks.My表中有2.1亿记录，这是40分钟以上。使用加入我得到的结果在8分钟。 –

@inquisitive_mind高兴你有一个决议。无法想象为什么你得到了40+分钟，除非你将数据加载到表变量中。 –

答

我看到你能够通过分区来实现这一点，但是我想向你展示另一种方法。您可以比较自己的表现并查看最快的表现 - （我假设分区更好）。

首先，我们知道甚至最新版本可能有多个日期，所以你真的不想为每个ID的最大版本，而是你想要的最大日期！

首先，设置：

DECLARE @table TABLE (ID INT, [Version] DECIMAL(18, 2), [TimeStamp] DATETIME) 

INSERT INTO @table 
VALUES 
(123, 1.5, '2015-03-28 08:21:04.563'), 
(123, 1.5, '2015-03-21 12:58:24.730'), 
(234, 1.5, '2016-10-15 23:08:09.550'), 
(345, 1.5, '2016-05-10 15:18:09.707'), 
(345, 1.5, '2016-09-02 21:30:00.657')

现在，让每个ID与最大日期：

SELECT ID, 
     MAX([TimeStamp]) AS MaxTimeStamp 
FROM @table 
GROUP BY ID

这给了我们什么，我们想：

ID   MaxTimeStamp 
----------- ----------------------- 
123   2015-03-28 08:21:04.563 
234   2016-10-15 23:08:09.550 
345   2016-09-02 21:30:00.657 

(3 row(s) affected)

现在我们只需要包含版本。这应该是很容易的，因为我们可以做ID和日期自联接：

SELECT T.ID, 
     MAX(T.[TimeStamp]) AS [MaxTimeStamp], 
     T2.[Version] AS [MaxVersion] 
FROM @table T 
JOIN @table T2 
    ON T.ID = T2.ID 
    AND T.[TimeStamp] = T2.[TimeStamp] 
GROUP BY T.ID, T2.[Version]

这为我们提供了以下结果：

ID   MaxTimeStamp   MaxVersion 
----------- ----------------------- --------------------------------------- 
123   2015-03-28 08:21:04.563 1.50 
234   2016-10-15 23:08:09.550 1.50 
345   2016-09-02 21:30:00.657 1.50 

(3 row(s) affected)

注意，我们要加入我们的原始查询到同样的表 - 按ID和日期，就像讨论过的一样。但是，我们现在选择的附加列，版本，所以它需要包含在GROUP BY

或者，您也可以通过使用CROSS APPLY获得相同的结果：

SELECT T.ID, 
     MAX(T.[TimeStamp]) AS MaxTimeStamp, 
     T2.[Version] 
FROM @table T 
CROSS APPLY 
    (
     SELECT Version 
     FROM @table T2 
     WHERE T2.ID = T.ID 
     AND T2.[TimeStamp] = T.[TimeStamp] 
    ) T2 
GROUP BY T.ID, T2.[Version]

当按两列分组时，获取最新记录SQL Server

相关推荐