如何在多表关联查询中将同一车辆的标签聚合为数组？

场景说明 #

需要关联查询cars、car_details和car_tags三张表，将同一car_id对应的所有tag聚合为数组形式返回。其中car_tags表结构及示例数据如下：

car_id |  tag
1      |   sport
1      |   coupe

以下是针对不同数据库的修改方案，替换原查询中的car_tag.tag为聚合后的标签数组：

MySQL/MariaDB 实现 #

方案1：生成逗号分隔字符串

使用GROUP_CONCAT函数拼接标签，默认用逗号分隔：

SELECT
  cars.id,
  cars.name,  
  cars.cover,
  car_details.category,
  car_details.max_kmh,
  car_details.producer,
  car_details.model,
  car_details.fuel,
  car_details.engine,
  car_details.ps,
  GROUP_CONCAT(car_tags.tag SEPARATOR ',') AS tags
FROM car_details
INNER JOIN cars
  ON car_details.car_id = cars.id
INNER JOIN car_tags
  ON car_tags.car_id = cars.id
GROUP BY cars.id, cars.name, cars.cover,
         car_details.category, car_details.max_kmh, car_details.producer,
         car_details.model, car_details.fuel, car_details.engine, car_details.ps;

方案2：生成JSON数组（MySQL 5.7+支持）

SELECT
  cars.id,
  cars.name,  
  cars.cover,
  car_details.category,
  car_details.max_kmh,
  car_details.producer,
  car_details.model,
  car_details.fuel,
  car_details.engine,
  car_details.ps,
  JSON_ARRAYAGG(car_tags.tag) AS tags
FROM car_details
INNER JOIN cars
  ON car_details.car_id = cars.id
INNER JOIN car_tags
  ON car_tags.car_id = cars.id
GROUP BY cars.id, cars.name, cars.cover,
         car_details.category, car_details.max_kmh, car_details.producer,
         car_details.model, car_details.fuel, car_details.engine, car_details.ps;

PostgreSQL 实现 #

使用ARRAY_AGG直接生成数组：

SELECT
  cars.id,
  cars.name,  
  cars.cover,
  car_details.category,
  car_details.max_kmh,
  car_details.producer,
  car_details.model,
  car_details.fuel,
  car_details.engine,
  car_details.ps,
  ARRAY_AGG(car_tags.tag) AS tags
FROM car_details
INNER JOIN cars
  ON car_details.car_id = cars.id
INNER JOIN car_tags
  ON car_tags.car_id = cars.id
GROUP BY cars.id, cars.name, cars.cover,
         car_details.category, car_details.max_kmh, car_details.producer,
         car_details.model, car_details.fuel, car_details.engine, car_details.ps;

SQL Server 实现 #

使用STRING_AGG生成逗号分隔字符串：

SELECT
  cars.id,
  cars.name,  
  cars.cover,
  car_details.category,
  car_details.max_kmh,
  car_details.producer,
  car_details.model,
  car_details.fuel,
  car_details.engine,
  car_details.ps,
  STRING_AGG(car_tags.tag, ',') AS tags
FROM car_details
INNER JOIN cars
  ON car_details.car_id = cars.id
INNER JOIN car_tags
  ON car_tags.car_id = cars.id
GROUP BY cars.id, cars.name, cars.cover,
         car_details.category, car_details.max_kmh, car_details.producer,
         car_details.model, car_details.fuel, car_details.engine, car_details.ps;

注意事项 #

1. 必须通过GROUP BY子句声明所有非聚合字段，否则会因数据库分组规则限制报错。
2. 若存在无标签的车辆，建议将INNER JOIN car_tags改为LEFT JOIN car_tags，可搭配COALESCE将空结果转为默认值（比如空数组、空字符串）。

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