使用group by时，如何获取最后一列

Question

有一个表_LogEventChar，其中包含如下所示的数据：

CharID          EventTime           EventID
--------------------------------------------------
1       2019-11-20 05:07:21.130         9   
1       2019-11-20 05:07:19.130         4   
1       2019-11-20 04:45:17.670         6   
1       2019-11-20 04:44:12.670         9   
1       2019-11-20 04:44:08.807         11  
1       2019-11-20 04:44:00.593         9   
1       2019-11-20 04:43:58.470         4       
1       2019-11-20 02:52:49.590         6   
1       2019-11-20 02:37:24.800         9   
1       2019-11-20 02:37:16.667         4   
8       2019-11-20 02:27:50.740         6
8       2019-11-20 02:26:33.567         4

如果我运行查询

SELECT CharID, MAX(EventTime) 'EventTime'
FROM dbo._LogEventChar with (NOLOCK)
WHERE EventID in (4, 6)
GROUP BY CharID

我会得到的结果是：

CharID      EventTime 
------------------------------------
1       2019-11-20 05:07:19.130
8       2019-11-20 02:27:50.740

如何获得以下结果：

CharID          EventTime           EventID
--------------------------------------------------
1       2019-11-20 05:07:19.130         4
8       2019-11-20 02:27:50.740         6

Answer 1

另一个选项是将WITH TIES与row_number()协同使用

示例

Select top 1 with ties * 
From  YourTable
WHERE EventID in (4, 6)
Order by row_number() over (partition by CharID order by EventTime desc)

退货

CharID  EventTime                EventID
1       2019-11-20 05:07:19.130  4
8       2019-11-20 02:27:50.740  6

Answer 2

也许您想要：

WITH CTE AS(
SELECT CharID,
EventTime,
EventID,
ROW_NUMBER() OVER (PARTITION BY CharID ORDER BY EventTime DESC) AS RN
FROM dbo._LogEventChar 
WHERE EventID in (4, 6))
SELECT CharID,
EventTime,
EventID
FROM CTE
WHERE RN = 1;

Answer 3

您想要过滤而不是聚合。在大多数RDBMS上，使用相关子查询的方法表现得非常好：

select t.*
from dbo._LogEventChar t
where 
EventID in (4, 6)
t.EventTime = (
select max(t1.EventTime)
from dbo._LogEventChar t1
where EventID in (4, 6) and t1.CharID = t.CharID
)

对于此查询的性能，请考虑(EventID, CharID, EventTime)上的索引。

另一个典型的解决方案是使用rank():

select CharID, EventTime, EventID
from (
select 
t.*,
rank() over(partition by CharID order by EventTime desc) rn
from dbo._LogEventChar t
where EventID in (4, 6)
) t
where rn = 1