如何在PARTITION BY查询中聚合OS.Id生成IdAggregate列？

你的现有查询已经按OS.returnmessageid、OS.unitnumber、OD.messageutc、OP.messagetype做了分区，要新增IdAggregate列存储同一分区下所有OS.Id的逗号分隔列表，可按以下方式实现：

情况1：SQL Server 2017及以上版本（推荐） #

直接使用官方提供的STRING_AGG字符串聚合函数，语法简洁高效：

SELECT OS.returnmessageid,
       OS.tagconfig,
       CASE
         WHEN OP.messagetype = 202
              AND OS.datapointid IN ( 1, 3 ) THEN
         CONVERT(NVARCHAR(max), OS.lookupvalue)
         ELSE Try_convert(Nvarchar(max), OS.value)
       END AS Value,
       OS.unitnumber,
       OD.messageutc,
       OP.messagetype,
       OS.isproccessed,
       OS.id,
       -- 新增的IdAggregate列，聚合同一分区下的所有OS.Id
       STRING_AGG(OS.id, ',') WITHIN GROUP (ORDER BY OS.id) 
         OVER (PARTITION BY OS.returnmessageid, OS.unitnumber, OD.messageutc, OP.messagetype) AS IdAggregate,
       Row_number()
         OVER (
           partition BY OS.returnmessageid, OS.unitnumber, OD.messageutc,
         OP.messagetype
           ORDER BY OS.returnmessageid, OS.unitnumber, OD.messageutc,
         OP.messagetype )
           AS RowNum
FROM   ooocommreturnmessagesscaling OS
       LEFT JOIN ooocommreturnmessagesparsing OP
              ON OP.id = OS.returnmessageid
       LEFT JOIN ooocommreturnmessagedetails OD
              ON OP.returnmessageid = OD.id

说明 #

• STRING_AGG(OS.id, ',') 指定要聚合的字段为OS.id，分隔符用逗号
• WITHIN GROUP (ORDER BY OS.id) 可选，用来定义聚合后ID的排序顺序
• OVER (PARTITION BY ...) 沿用你现有查询的分区字段，确保只聚合同一分区内的ID

情况2：SQL Server 2016及更早版本 #

如果你的版本不支持STRING_AGG，可以用STUFF结合FOR XML PATH的经典方法实现：

SELECT OS.returnmessageid,
       OS.tagconfig,
       CASE
         WHEN OP.messagetype = 202
              AND OS.datapointid IN ( 1, 3 ) THEN
         CONVERT(NVARCHAR(max), OS.lookupvalue)
         ELSE Try_convert(Nvarchar(max), OS.value)
       END AS Value,
       OS.unitnumber,
       OD.messageutc,
       OP.messagetype,
       OS.isproccessed,
       OS.id,
       -- 新增的IdAggregate列
       STUFF((
           SELECT ',' + CAST(OS2.id AS VARCHAR(max))
           FROM ooocommreturnmessagesscaling OS2
           LEFT JOIN ooocommreturnmessagesparsing OP2 ON OP2.id = OS2.returnmessageid
           LEFT JOIN ooocommreturnmessagedetails OD2 ON OP2.returnmessageid = OD2.id
           -- 匹配同一分区的条件
           WHERE OS2.returnmessageid = OS.returnmessageid
             AND OS2.unitnumber = OS.unitnumber
             AND OD2.messageutc = OD.messageutc
             AND OP2.messagetype = OP.messagetype
           ORDER BY OS2.id
           FOR XML PATH(''), TYPE
       ).value('.', 'VARCHAR(max)'), 1, 1, '') AS IdAggregate,
       Row_number()
         OVER (
           partition BY OS.returnmessageid, OS.unitnumber, OD.messageutc,
         OP.messagetype
           ORDER BY OS.returnmessageid, OS.unitnumber, OD.messageutc,
         OP.messagetype )
           AS RowNum
FROM   ooocommreturnmessagesscaling OS
       LEFT JOIN ooocommreturnmessagesparsing OP
              ON OP.id = OS.returnmessageid
       LEFT JOIN ooocommreturnmessagedetails OD
              ON OP.returnmessageid = OD.id

说明 #

• 子查询通过FOR XML PATH('')将同一分区的ID拼接成带前置逗号的字符串（比如,1,2,3）
• STUFF(..., 1, 1, '') 去掉字符串开头的第一个逗号，最终得到1,2,3的格式
• 子查询的连接条件与主查询完全一致，确保只匹配同一分区的数据

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