sql - 如何删除复杂 SQL 查询中的某些重复项

Question

我正在编写一个查询，需要它来删除 a.GenUserID 的所有重复项，同时保留最近的登录日期(即 b.LogDateTime)，但该日期必须早于 6 个月。如果有更晚的日期，则必须将其删除。我希望这是有道理的。

SELECT DISTINCT 
    a.GenUserID, 
    c.DeletionDate, 
    b.LogDateTime,
    (CASE c.Disabled WHEN 0 THEN 'NO' else 'YES - ARCHIVED' end)
FROM RioReport.dbo.GenUser a 
LEFT JOIN dbo.GenUserArchive c on a.GenUserID = c.GenUserID
LEFT JOIN dbo.GenUserAccessHistory b on a.GenUserID = b.ExtraInfo
WHERE(a.Disabled=0 or c.Disabled=0)
    AND c.DeletionDate IS NOT NULL
    AND ((DateAdd(MM, -6, GetDate()) > b.LogDateTime or b.LogDateTime IS NULL))
ORDER BY a.GenUserID, b.LogDateTime desc

Answer 1

您可以将 row_number() 信息添加到您的查询中，并将该查询包装到一个外部查询中，该查询仅从该结果中获取编号为 1 的记录:

select      *
from        (
    select      a.GenUserID, 
                c.DeletionDate, 
                b.LogDateTime,
                case c.Disabled when 0 then 'NO' else 'YES - ARCHIVED' end as diabled,
                row_number() over (partition by a.GenUserID
                                   order by     b.LogDateTime desc) as rn
    from        RioReport.dbo.GenUser a 
    inner join  dbo.GenUserArchive c
            on  a.GenUserID = c.GenUserID
    left join   dbo.GenUserAccessHistory b
            on  a.GenUserID = b.ExtraInfo
    where       (a.Disabled=0 or c.Disabled=0)
    and         c.DeletionDate is not null
    and         (DateAdd(MM, -6, GetDate()) > b.LogDateTime or b.LogDateTime is null)
    )
where       rn = 1            
order by    a.GenUserID

请注意，您可以将第一个 left join 转换为 inner join 而无需对结果集进行任何更改，因为您对其其中一个进行了非空检查领域。 inner join 是首选，它可能会提高性能。

如果 GenUserAccessHistory.LogDateTime 始终不为空，则可以通过移动 DateAdd(MM, - 6、GetDate()) > b.LogDateTime条件到适当的加入on子句。

生成的行号将按照 LogDateTime 值的降序排列，并为每个不同的用户从 1 重新开始。

没有窗口函数的替代方案

自 SQL Server 2008 起支持

row_number() 和其他窗口函数。在您编写的注释中您不能使用它。如果是这种情况，这里有一个使用公用表表达式的替代方法(自 SQL Server 2005 起支持):

;with cte as (
    select      a.GenUserID, 
                c.DeletionDate, 
                b.LogDateTime,
                case c.Disabled when 0 then 'NO' else 'YES - ARCHIVED' end as disabled,
    from        RioReport.dbo.GenUser a 
    inner join  dbo.GenUserArchive c
            on  a.GenUserID = c.GenUserID
    left join   dbo.GenUserAccessHistory b
            on  a.GenUserID = b.ExtraInfo
    where       (a.Disabled=0 or c.Disabled=0)
    and         c.DeletionDate is not null
    and         (DateAdd(MM, -6, GetDate()) > b.LogDateTime or b.LogDateTime is null)
    )    
select      *
from        cte main
where       LogDateTime is null
        or  not exists (select   1
                        from     cte sub
                        where    sub.GenUserID = main.GenUserID
                        and      sub.LogDateTime > main.LogDateTime)
order by    GenUserID