SQL Server 2014中STRING_AGG替代查询错误排查

问题背景 #

你有一张tbl_DailyLogMaster表，想要按UserID和deviceID聚合每日的activityName与createdDate信息。在支持STRING_AGG的SQL Server版本中，你的查询能正常返回预期结果，但在2014版本中用FOR XML PATH编写的替代查询却出现了内容格式混乱、记录重复的问题。

现有替代查询的错误点 #

你的查询主要存在三个关键错误：

• 关联条件不完整：子查询仅关联了UserID，未关联deviceID，导致聚合时会把同一UserID下所有deviceID的记录混在一起，这就是tag内容混乱的核心原因。
• GROUP BY字段冗余：主查询的GROUP BY里包含了createdDate和activityName这两个明细字段，会把原本应聚合为一行的记录拆分成多条，因此同一UserID和deviceID的记录重复出现。
• 日期格式与STUFF参数错误：
  • CONVERT(varchar, createdDate)未指定样式，默认转换会生成Jan 9 2020 12:51AM这类非标准格式，和原查询的格式不一致。
  • 子查询生成的字符串开头没有多余字符，但你使用了STUFF(...,1,1,'')，这会删掉第一个字符（比如ON变成N），导致tag里出现N - ...的错误内容。

修正后的查询 #

针对这些问题，我调整了查询语句：

SELECT 
    deviceID, 
    UserID, 
    STUFF((
        SELECT ' | ' + activityName + ' - ' + CONVERT(varchar, createdDate, 121)
        FROM tbl_DailyLogMaster t2
        WHERE t2.UserID = t1.UserID 
          AND t2.deviceID = t1.deviceID
          AND CONVERT(date, t2.createdDate) = CONVERT(date, GETDATE())
        ORDER BY t2.createdDate  -- 按时间排序，保证ON/OFF顺序符合逻辑
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 3, '') AS tag
FROM tbl_DailyLogMaster t1
WHERE CONVERT(date, t1.createdDate) = CONVERT(date, GETDATE())
GROUP BY UserID, deviceID;

修正说明： #

1. 完善关联条件：子查询同时关联UserID和deviceID，确保仅聚合同一用户同一设备的记录。
2. 精简GROUP BY字段：只保留UserID和deviceID，保证每个用户设备组合仅返回一行。
3. 规范日期格式：使用CONVERT(varchar, createdDate, 121)指定日期样式，输出与STRING_AGG版本一致的yyyy-mm-dd hh:mi:ss.mmm格式。
4. 正确处理分隔符：子查询先添加|分隔符，再用STUFF去掉开头的3个字符（对应|的长度）；同时用TYPE和.value('.', 'NVARCHAR(MAX)')避免XML转义特殊字符的问题。
5. 添加排序：子查询按createdDate排序，保证活动记录的顺序与时间一致，逻辑更合理。

验证结果 #

修正后的查询会返回与STRING_AGG版本完全一致的结果：

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