MySQL按post_id分组聚合多字段不同值的实现方法咨询

嘿，我来帮你搞定这个分组聚合的需求！你想要按post_id把关联的用户ID、名称、头像路径分别聚合成集合格式，其实核心是用分组拼接类的聚合函数，不同数据库的实现略有差异，我给你列几个主流数据库的解决方案：

MySQL 实现方案 #

MySQL里最常用的是GROUP_CONCAT()函数，它能把分组内的字段值拼接成字符串，再配合CONCAT()就能生成你要的{xxx,xxx}格式：

SELECT 
    post_id,
    -- 拼接用户ID集合
    CONCAT('{', GROUP_CONCAT(DISTINCT user_id SEPARATOR ','), '}') AS user_ids,
    -- 拼接用户名集合
    CONCAT('{', GROUP_CONCAT(DISTINCT user_name SEPARATOR ','), '}') AS user_names,
    -- 拼接头像路径集合
    CONCAT('{', GROUP_CONCAT(DISTINCT profile_image SEPARATOR ','), '}') AS profile_images
FROM 
    your_table_name -- 替换成你的实际表名
GROUP BY 
    post_id;

• 加DISTINCT是为了避免同一post下出现重复的用户数据，如果你的数据不会重复，可以去掉它
• 如果想要更规范的JSON数组格式（比如[10,11]），MySQL 5.7+可以用JSON_ARRAYAGG()替代GROUP_CONCAT()：

SELECT 
    post_id,
    JSON_ARRAYAGG(user_id) AS user_ids,
    JSON_ARRAYAGG(user_name) AS user_names,
    JSON_ARRAYAGG(profile_image) AS profile_images
FROM 
    your_table_name
GROUP BY 
    post_id;

PostgreSQL 实现方案 #

PostgreSQL用string_agg()做字符串拼接，或者array_agg()直接生成数组（刚好默认显示就是{xxx,xxx}格式，完美匹配你的需求）：

字符串集合格式 #

SELECT 
    post_id,
    CONCAT('{', string_agg(DISTINCT user_id::text, ','), '}') AS user_ids,
    CONCAT('{', string_agg(DISTINCT user_name, ','), '}') AS user_names,
    CONCAT('{', string_agg(DISTINCT profile_image, ','), '}') AS profile_images
FROM 
    your_table_name
GROUP BY 
    post_id;

原生数组格式（更推荐） #

PostgreSQL的数组类型本身就用大括号包裹，直接用array_agg()就能得到你要的效果：

SELECT 
    post_id,
    array_agg(DISTINCT user_id) AS user_ids,
    array_agg(DISTINCT user_name) AS user_names,
    array_agg(DISTINCT profile_image) AS profile_images
FROM 
    your_table_name
GROUP BY 
    post_id;

SQL Server 实现方案 #

SQL Server 2017及以上支持STRING_AGG()函数，用法如下：

SELECT 
    post_id,
    CONCAT('{', STRING_AGG(user_id, ',') WITHIN GROUP (ORDER BY user_id), '}') AS user_ids,
    CONCAT('{', STRING_AGG(user_name, ',') WITHIN GROUP (ORDER BY user_id), '}') AS user_names,
    CONCAT('{', STRING_AGG(profile_image, ',') WITHIN GROUP (ORDER BY user_id), '}') AS profile_images
FROM 
    your_table_name
GROUP BY 
    post_id;

• WITHIN GROUP (ORDER BY user_id)用来指定拼接的顺序，确保结果稳定，不需要的话可以去掉
• 如果需要去重，STRING_AGG()本身不支持DISTINCT，可以先通过子查询去重再聚合：

WITH unique_post_users AS (
    SELECT DISTINCT post_id, user_id, user_name, profile_image
    FROM your_table_name
)
SELECT 
    post_id,
    CONCAT('{', STRING_AGG(user_id, ','), '}') AS user_ids,
    CONCAT('{', STRING_AGG(user_name, ','), '}') AS user_names,
    CONCAT('{', STRING_AGG(profile_image, ','), '}') AS profile_images
FROM 
    unique_post_users
GROUP BY 
    post_id;

关于你尝试的方法说明 #

你之前用的GROUP BY是核心基础，必须按post_id分组；JOIN在这里其实不需要，因为数据都在同一张表里；json_object是用来生成单个JSON对象的，而我们需要的是多个值的集合，所以应该用数组/字符串拼接的聚合函数来实现。

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