MySQL查询需求：按品牌分组聚合多附加ID与字段值

要实现按brand_name分组，将对应品牌的多行variant_color_id、additional_id和additional_field_value聚合为单行展示，可使用MySQL的GROUP_CONCAT()聚合函数完成字段值的拼接。

修改后的SQL语句如下：

SELECT
    vehicle_brand.brand_name,
    GROUP_CONCAT(DISTINCT variant_color.id SEPARATOR ', ') AS variant_color_id,
    GROUP_CONCAT(DISTINCT additiona_field_line.additional_id SEPARATOR ', ') AS additional_id,
    GROUP_CONCAT(DISTINCT additiona_field_line.additional_field_value SEPARATOR ', ') AS additional_field_value
FROM `vehicle_price_mapper` AS vpm
LEFT JOIN vehicle_fuel_mapper AS vvm ON vvm.id = vpm.vehicle_fuel_mapper_id            
LEFT JOIN vehicle_model ON vehicle_model.id = vvm.v_model_id
LEFT JOIN vehicle_brand ON vehicle_brand.id = vehicle_model.brand_id
LEFT JOIN fuel_master ON fuel_master.id = vvm.fuel_id
LEFT JOIN variant_color ON variant_color.vpm_id = vpm.id
LEFT JOIN additiona_field_line ON additiona_field_line.variant_color_id = variant_color.id
WHERE vehicle_brand.brand_name IN ('BMW', 'Mahindra')
GROUP BY vehicle_brand.brand_name;

关键说明： #

• GROUP_CONCAT()：将分组内指定字段的所有值拼接成单个字符串，默认用逗号分隔，也可通过SEPARATOR自定义分隔符（如; ）。
• DISTINCT：如果分组内存在重复字段值，添加该关键字可去重，避免重复内容拼接。
• WHERE子句：通过IN筛选目标品牌，确保结果仅返回BMW和Mahindra两行数据。

注意事项： #

MySQL默认限制GROUP_CONCAT拼接结果的长度为1024字符，若需拼接的内容较长，可通过调整会话参数扩大上限：

SET SESSION group_concat_max_len = 10000;

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