Azure DevOps Server用户访问记录去重及最大时间查询问题

看起来你遇到的核心问题是原查询没正确关联访问记录表，分组逻辑也没到位，导致重复记录满天飞，MAX(StartTime)还出了全局统一值的问题。我来帮你修正这个查询，精准满足「每个用户每天每个项目仅保留一条当天最后访问时间」的需求。

原查询的问题拆解 #

1. 表关联完全缺失：原查询里tbl_command和其他三张表没有任何关联条件，相当于做了笛卡尔积，这不仅会产生大量无效重复数据，还导致StartTime根本没和对应的用户、项目绑定。
2. 分组逻辑错误：之前用GROUP BY时没指定正确的分组维度，直接调用MAX(StartTime)自然会算出全局最大值，而非每个用户-项目-日期组合的最大值。
3. 旧风格连接易出错：用逗号连接表的写法可读性差，很容易漏掉关联条件，建议换成显式JOIN语法。

修正后的SQL查询 #

SELECT
    obj1.DisplayName AS [User Name],
    obj1.SamAccountName AS [R-User],
    obj2.DisplayName AS [Projekt Name],
    CAST(cmd.StartTime AS DATE) AS [Access Date],
    MAX(cmd.StartTime) AS [Last Access Time]
FROM
    ADObjects obj1
JOIN
    ADObjectMemberships mem ON obj1.ObjectSID = mem.MemberObjectSID
JOIN
    ADObjects obj2 ON mem.ObjectSID = obj2.ObjectSID
JOIN
    tbl_command cmd ON obj1.SamAccountName = cmd.IdentityName
WHERE
    obj1.DisplayName NOT LIKE '%\%'
    AND obj1.DisplayName NOT LIKE 'APP_%'
    AND obj1.DisplayName NOT LIKE 'CLI_%'
    AND obj1.DisplayName NOT LIKE '%admin%'
    AND obj1.DisplayName NOT LIKE '%svc%'
GROUP BY
    obj1.DisplayName,
    obj1.SamAccountName,
    obj2.DisplayName,
    CAST(cmd.StartTime AS DATE)
ORDER BY
    [Projekt Name],
    [User Name],
    [Access Date] DESC;

关键优化点说明 #

1. 精准关联访问记录：通过obj1.SamAccountName = cmd.IdentityName把用户的访问日志（tbl_command）和用户、项目信息绑定，确保时间是对应用户访问该项目的真实时间。
2. 按日期维度分组：用CAST(cmd.StartTime AS DATE)提取访问日期，把同一天的访问记录归为一组，再用MAX(cmd.StartTime)取当天的最后访问时间点。
3. 显式JOIN更清晰：替代旧的逗号连接写法，表与表之间的关联关系一目了然，避免意外的笛卡尔积。
4. 完整的分组字段：GROUP BY包含了所有非聚合字段（用户名称、用户账号、项目名称、访问日期），确保MAX函数是基于每个独立分组计算的，不会出现全局统一值。

这样查询返回的结果就是每个用户、每个项目、每天仅一条记录，且是当天该用户访问此项目的最后时间点。

内容的提问来源于stack exchange，提问作者Juggybash