sql - 根据某些标准选择重复项

Question

我有一个表格，记录用户激活和停用模块。我只想选择表中激活和停用之间具有最大时间差的那些行。

user        activation           Deactivation     usage  dmb
Pejman   2005-10-04 14:02:00   2012-09-28 18:29:00   23   198
Pejman   2006-05-30 12:44:00   2012-09-28 18:29:00   34   198
Pejman   2009-11-18 11:06:00   2012-09-28 18:29:00   64   198
Shahin   2005-02-11 10:53:00   2012-09-28 18:29:00   52   323
Shahin   2012-06-24 08:35:00   2012-09-28 18:29:00   17   323
Shahin   2005-02-24 14:22:00   2006-01-16 09:03:00   19   323
Kazem    2008-01-21 22:32:00   2011-01-01 00:00:00   73   666
Kazem    2008-01-21 22:35:00   2012-09-28 18:29:00   62   666

我想要的输出

用户

Pejman   2005-10-04 14:02:00   2012-09-28 18:29:00   23   198
Shahin   2005-02-11 10:53:00   2012-09-28 18:29:00   52   323
Kazem    2008-01-21 22:35:00   2012-09-28 18:29:00   62   666

查找持续时间(激活和停用之间的差异)我正在使用以下 sql 代码(MSSQL)

 datediff(d,isnull(deactivation, getdate()), activation) AS Time_Difference

编辑

我的表是“with”操作的结果。意思是得到这样的第一个表，我写了一些其他查询，如下所示

with mytab_cte(user, editionID, size, CompanyName, cvr, postcode, agreement, ActivationT, DeactivationT) 
as 
( 
select ops.Opsaetning as opsaetning, A.EditionID as editionID, b.FilStr as size, v.Navn as CompanyName, v.CVR as cvr, v.Postnr as postcode, A.AftaleNr, BMA.Tilmeldt as ActivationT, BMA.Frameldes as DeactivationT
from BilagsAttachment as b 
inner join Opsaetning as ops on b.Opsaetning = ops.Opsaetning 
inner join Virksomhed as v on v.Aftalenr = ops.Aftalenr 
inner join Aftalenr as A on A.AftaleNr = v.Aftalenr 
inner join BenyttetModulAftalenr as BMA on BMA.Aftalenr = ops.Aftalenr

where ISNULL(v.SkalFaktureres, 0) = 1 
AND ISNULL(v.ProeveVirksomhed, 0) = 0 
AND A.Spaerret = 0 
AND (A.FrameldingsDato IS NULL 
OR A.FrameldingsDato > GETDATE()) 
AND v.TilSletning = 0 
AND V.Email 
) 

Answer 1

这看起来像 SQL 服务器。在这种情况下...

select * from
(
    select *, 
        row_number() over 
        (
            partition by [user]
            order by datediff(d,isnull(deactivation, getdate()), activation) desc
        ) as rn
    from yourtable
) v
where rn = 1