按几个列分组，包括MAX()以限制行，但显示不在分组依据中的列

Question

我每5分钟向日志表输出一次sp_whoisative，并希望聚合诸如运行时间或cpu之类的列。我获得唯一记录的最佳机会是session_id、login_time、login_name，我想要每个唯一记录的MAX(collection_time(。

我的问题是如何在一个查询中做到这一点？

我尝试了各种各样的变化，但始终无法获得所需的输出。我最终完成了两个步骤：1(分组到##Temp表，2(然后将##Temp加入日志表，以获得我想要的报告中的所有列。

步骤1查询：

enter code hereIF(OBJECT_ID('tempdb..##TEMP') IS NOT NULL) BEGIN DROP TABLE ##TEMP END
SELECT *
INTO ##TEMP
FROM (
select distinct Session_id, login_name, login_time, max(collection_time) as MaxCollection
from [MyDB].[DBA].[WhoIsActive]
group by login_name,  session_id, login_time
) AS X

步骤2查询：

DECLARE @yesterday DATETIME, @today DATETIME
SET @yesterday = DATEADD(hh,12,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-1),0))
SET @today = DATEADD(hh,11,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0))
SELECT 
tt.[session_id]
,tt.[tasks]
,tt.[login_time]
,tt.[login_name]
,tt.[start_time]
,tt.[collection_time]      
,tt.[dd hh:mm:ss.mss] as ElapsedTime
,(Substring(tt.[dd hh:mm:ss.mss],1,2) * 86400) + (Substring(tt.[dd hh:mm:ss.mss],4,2) * 3600) + (Substring(tt.[dd hh:mm:ss.mss],7,2) * 60) + (Substring(tt.[dd hh:mm:ss.mss],10,2)) as totalseconds
,tt.[host_name]
,tt.[status]
,tt.[CPU]
,tt.[program_name]
FROM [MyDB].[DBA].[WhoIsActive] tt
right JOIN ##TEMP kk
on tt.session_id = kk.session_id and tt.collection_time = kk.MaxCollection
--where kk.start_time between @yesterday and @today --and kk.login_name like '%ETL%'
order by tt.login_name asc, tt.session_id asc, login_time desc

Answer 1

我获得唯一记录的最佳机会是session_id、login_time、login_name，并且我想要每个唯一记录的MAX(collection_time)。

如果您只想要每个元组的最大收集时间，那么使用聚合：

select session_id, login_time, login_name, max(collection_time) max_collection_time
from whoisactive
group by session_id, login_time, login_name

另一方面，如果您想要整个对应的行，那么一个选项是row_number():

select *
from (
select w.*,
row_number() over(partition by session_id, login_time, login_name order by collection_time desc) rn
from whoisactive w
) t
where rn = 1