SQL实现按DetailRecordId和ColumnName分组，每2个字段值合并

现有一张名为Images的表，表结构及数据如下：

TableName    ColumnName     RecordId    Caption          ImageType      ROTId     DetailRecordId
Table2      PAUTDPhoto_bin  1462        test            PAUTPhotos      1041383   11480170
Table2      PAUTDPhoto_bin  1463        test1           PAUTPhotos      1041383   11480170
Table2      PAUTDPhoto_bin  1464        testing photo   PAUTPhotos      1041383   11480170
Table1      ItemPhoto_bin   11480170    caption          ItemPhoto      1041383   11480170
Table1      ItemPhoto_bin   11480171    test photo       ItemPhoto      1041383   11480171
Table1      ItemPhoto_bin   11480172    description      ItemPhoto      1041383   11480172
Table2      PAUTDPhoto_bin  1465        test             PAUTPhotos     1041383   11480172
Table2      PAUTDPhoto_bin  1466        55               PAUTPhotos     1041383   11480172

需求 #

• 按DetailRecordId和ColumnName分组；
• 分组后将RecordId和Caption列的每2个值用$合并为一个值，剩余单个值保持原样。

期望输出 #

TableName    ColumnName     RecordId    Caption          ImageType      ROTId     DetailRecordId
Table2      PAUTDPhoto_bin  1462$1463   test$test1       PAUTPhotos     1041383   11480170
Table2      PAUTDPhoto_bin  1464        testing photo    PAUTPhotos     1041383   11480170
Table1      ItemPhoto_bin   11480170    caption          ItemPhoto      1041383   11480170
Table1      ItemPhoto_bin   11480171    test photo       ItemPhoto      1041383   11480171
Table1      ItemPhoto_bin   11480172    description      ItemPhoto      1041383   11480172
Table2      PAUTDPhoto_bin  1465$1466   test$55          PAUTPhotos     1041383   11480172

解决方案 #

SQL Server 实现 #

核心思路是先给每组内的行分配序号，再通过序号整除运算将每2行归为一组，最后合并对应字段：

WITH RankedImages AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY DetailRecordId, ColumnName ORDER BY RecordId) AS rn
    FROM Images
),
GroupedPairs AS (
    SELECT 
        TableName,
        ColumnName,
        ImageType,
        ROTId,
        DetailRecordId,
        (rn - 1) / 2 AS group_num,
        RecordId,
        Caption
    FROM RankedImages
)
SELECT 
    TableName,
    ColumnName,
    STRING_AGG(RecordId, '$') WITHIN GROUP (ORDER BY rn) AS RecordId,
    STRING_AGG(Caption, '$') WITHIN GROUP (ORDER BY rn) AS Caption,
    ImageType,
    ROTId,
    DetailRecordId
FROM GroupedPairs
GROUP BY TableName, ColumnName, ImageType, ROTId, DetailRecordId, group_num
ORDER BY DetailRecordId, ColumnName, group_num;

MySQL 实现 #

将STRING_AGG替换为GROUP_CONCAT即可适配：

WITH RankedImages AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY DetailRecordId, ColumnName ORDER BY RecordId) AS rn
    FROM Images
),
GroupedPairs AS (
    SELECT 
        TableName,
        ColumnName,
        ImageType,
        ROTId,
        DetailRecordId,
        FLOOR((rn - 1)/2) AS group_num,
        RecordId,
        Caption
    FROM RankedImages
)
SELECT 
    TableName,
    ColumnName,
    GROUP_CONCAT(RecordId ORDER BY rn SEPARATOR '$') AS RecordId,
    GROUP_CONCAT(Caption ORDER BY rn SEPARATOR '$') AS Caption,
    ImageType,
    ROTId,
    DetailRecordId
FROM GroupedPairs
GROUP BY TableName, ColumnName, ImageType, ROTId, DetailRecordId, group_num
ORDER BY DetailRecordId, ColumnName, group_num;

逻辑说明 #

1. RankedImages：通过ROW_NUMBER()给每个(DetailRecordId, ColumnName)分组内的行按RecordId排序并生成连续序号，保证行顺序稳定。
2. GroupedPairs：用(rn-1)/2计算分组编号，让每2行归为同一组。
3. 最终查询：通过聚合函数合并同组内的RecordId和Caption，按要求分组后输出结果。

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