请求协助优化获取去重数据的SQL查询

嘿，我明白你的困扰了——你本来想基于_System_objNBS获取唯一的记录结果，但因为同一个_System_objNBS对应了不同的_System_CurrentStage、修改时间等字段值，导致DISTINCT没办法帮你实现预期的去重效果对吧？

咱们先拆解下问题：你当前用的SELECT DISTINCT是把所有选中的字段组合起来判断唯一性的，只要其中任何一个字段的值不一样，这条记录就会被当成新的结果返回。所以同一个_System_objNBS只要有不同的阶段、修改时间，就会输出多条记录。

根据你的需求，我给你两种常见的解决方案：

方案一：获取每个_System_objNBS的最新状态记录 #

如果你想要的是每个_System_objNBS对应的最新阶段、最新修改时间的那条记录，可以用窗口函数ROW_NUMBER()来实现分组排序，取每组的第一条：

WITH ranked_records AS (
    SELECT 
        d._System_objNBS,
        d._System_CurrentStage,
        d.CreatedDate,
        d.dateModified,
        d.fld016535 AS Originator_Dept,
        e.fld004216,
        e.fld003984,
        -- 按_System_objNBS分组，按dateModified倒序排序，最新的记录排第1
        ROW_NUMBER() OVER (PARTITION BY d._System_objNBS ORDER BY d.dateModified DESC) AS rn
    FROM Obj122 d
    -- 这里如果Obj81是用户信息表，LEFT JOIN可能比FULL JOIN更合适，避免多出不必要的空记录
    LEFT JOIN Obj81 e ON d.UserCreatedId = e.UserId
    WHERE d._System_CurrentStage NOT IN ('Release')
      AND d.dateModified < '2024-06-30'
)
SELECT 
    _System_objNBS,
    _System_CurrentStage,
    CreatedDate,
    dateModified,
    Originator_Dept,
    fld004216,
    fld003984
FROM ranked_records
WHERE rn = 1;

方案二：按_System_objNBS聚合其他字段 #

如果你的需求是对同一个_System_objNBS的其他字段做聚合（比如保留最早的创建时间、合并阶段信息等），可以用GROUP BY配合聚合函数：

SELECT 
    d._System_objNBS,
    -- 比如取第一个出现的阶段，或者用STRING_AGG合并所有阶段（如果数据库支持）
    MAX(d._System_CurrentStage) AS Latest_CurrentStage,
    MIN(d.CreatedDate) AS Earliest_CreatedDate,
    MAX(d.dateModified) AS Latest_dateModified,
    -- 假设同一个_System_objNBS的Originator_Dept是固定的，直接取即可
    MAX(d.fld016535) AS Originator_Dept,
    -- 用户信息如果是唯一的，直接取对应的字段
    MAX(e.fld004216) AS fld004216,
    MAX(e.fld003984) AS fld003984
FROM Obj122 d
LEFT JOIN Obj81 e ON d.UserCreatedId = e.UserId
WHERE d._System_CurrentStage NOT IN ('Release')
  AND d.dateModified < '2024-06-30'
GROUP BY d._System_objNBS;

另外提个小建议：当前SQL里的FULL JOIN如果不是必须的，换成LEFT JOIN会更合理——因为FULL JOIN会把Obj81中没有匹配UserCreatedId的记录也拉出来，可能导致结果出现更多不必要的空值记录，反而影响去重效果。

备注：内容来源于stack exchange，提问作者Aniket Arulkar KPCL