如何在SQL分组查询中添加展示ID关联逻辑的connection列？

核心思路是利用字符串聚合函数，将同分组内的ID-喜好颜色-时期拼接成指定格式的字符串，作为connection列返回。以下分主流数据库给出具体实现：

假设表结构 #

假设你的数据表名为color_preferences，字段包括：id（用户ID）、group_name（分组标识）、favorite_color（喜好颜色）、from_when（时期：child/adult）、points（积分）

1. MySQL/MariaDB 实现 #

使用GROUP_CONCAT函数完成字符串聚合：

SELECT
    group_name,
    favorite_color,
    from_when,
    SUM(points) AS total_points,
    -- 按ID排序拼接成指定格式
    GROUP_CONCAT(CONCAT(id, '-', favorite_color, '-', from_when) ORDER BY id SEPARATOR ', ') AS connection
FROM color_preferences
-- 按原查询的分组维度聚合
GROUP BY group_name, favorite_color, from_when
-- 筛选积分总和大于70的分组
HAVING SUM(points) > 70;

2. PostgreSQL 实现 #

使用STRING_AGG函数：

SELECT
    group_name,
    favorite_color,
    from_when,
    SUM(points) AS total_points,
    -- 按ID排序拼接
    STRING_AGG(CONCAT(id, '-', favorite_color, '-', from_when), ', ' ORDER BY id) AS connection
FROM color_preferences
GROUP BY group_name, favorite_color, from_when
HAVING SUM(points) > 70;

3. SQL Server 实现 #

2017及以上版本（支持STRING_AGG） #

SELECT
    group_name,
    favorite_color,
    from_when,
    SUM(points) AS total_points,
    STRING_AGG(CONCAT(id, '-', favorite_color, '-', from_when), ', ') WITHIN GROUP (ORDER BY id) AS connection
FROM color_preferences
GROUP BY group_name, favorite_color, from_when
HAVING SUM(points) > 70;

2016及以下版本（用STUFF+FOR XML PATH模拟） #

-- 先筛选出符合积分条件的分组
WITH valid_groups AS (
    SELECT
        group_name,
        favorite_color,
        from_when,
        SUM(points) AS total_points
    FROM color_preferences
    GROUP BY group_name, favorite_color, from_when
    HAVING SUM(points) > 70
)
SELECT
    vg.group_name,
    vg.favorite_color,
    vg.from_when,
    vg.total_points,
    -- 拼接同组内的关联信息
    STUFF(
        (SELECT ', ' + CONCAT(cp.id, '-', cp.favorite_color, '-', cp.from_when)
         FROM color_preferences cp
         WHERE cp.group_name = vg.group_name
           AND cp.favorite_color = vg.favorite_color
           AND cp.from_when = vg.from_when
         ORDER BY cp.id
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
        1, 2, '' -- 去掉开头多余的逗号空格
    ) AS connection
FROM valid_groups vg;

关键说明 #

• 分组维度必须和原查询保持一致（group_name、from_when，加上favorite_color确保按颜色分组的逻辑正确）
• 聚合时按id排序可以让拼接结果更规整
• 不同数据库的字符串聚合函数语法有差异，需根据实际使用的数据库选择对应方案

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