SQL Server去重时按唯一datetime排序拼接字段的实现方法

错误原因分析 #

• 原查询中先执行distinct时丢弃了callDateTime字段，后续生成行号时仅按callID排序，没有指定时间排序规则，导致返回顺序随机
• 去重逻辑和排序逻辑分离，无法保留重复数据对应的最早时间戳，自然无法按事件发生顺序拼接

正确实现代码 #

SELECT 
  CallID, 
  CONCAT([1], ',', [2], ',', [3]) AS combined_history
FROM (
  SELECT 
    CallID, 
    historyEdit, 
    ROW_NUMBER() OVER (PARTITION BY callID ORDER BY callDateTime) AS rowNum
  FROM (
    SELECT 
      callID,
      historyEdit,
      callDateTime,
      -- 相同callID+相同historyEdit只保留最早的一条，实现去重
      ROW_NUMBER() OVER (PARTITION BY callID, historyEdit ORDER BY callDateTime) AS dup_rn
    FROM (
      SELECT 
        callID,
        callDateTime,
        SUBSTRING(history, 0, CHARINDEX(', ID:', history)) AS historyEdit
      FROM test
    ) t1
  ) t2
  WHERE dup_rn = 1 -- 过滤重复的historyEdit
) t3
PIVOT(MAX(historyEdit) FOR rowNum IN ([1],[2],[3])) piv

逻辑说明 #

• 最内层查询先裁剪history字段，同时保留原始callDateTime用于排序
• 第二层查询按callID+historyEdit分组，每组内按时间升序排序生成重复标记dup_rn，取dup_rn=1实现去重，同时保留每个唯一historyEdit对应的最早时间
• 第三层查询对去重后的结果按时间升序生成行号，保证顺序符合事件发生先后
• 最后通过PIVOT拼接结果，顺序完全符合预期

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