PostgreSQL查询优化：实现图片ID唯一返回并聚合标签ID

核心思路 #

要让每个图片ID仅显示一次，核心是按图片维度分组，把关联的多个标签ID聚合为一个数组（或拼接字符串），而非按「图片+标签」的组合分组。不同数据库的聚合函数存在差异，以下分场景给出具体方案：

1. MySQL 环境 #

• 若使用 MySQL 5.7 及以下版本：用 GROUP_CONCAT 拼接标签ID，再包装成数组格式
• MySQL 8.0+ 版本：推荐用 JSON_ARRAYAGG 直接生成标准JSON数组

兼容低版本的写法

SELECT 
  p.created_date,
  p.id AS picture_id,
  CONCAT('[ ', GROUP_CONCAT(t.id SEPARATOR ', '), ' ]') AS tagIds
FROM pictures p
INNER JOIN picture_tags pt ON p.id = pt.picture_id
INNER JOIN tags t ON pt.tag_id = t.id
WHERE pt.tag_id IN (1, 2)
GROUP BY p.id, p.created_date
ORDER BY p.created_date ASC;

MySQL 8.0+ 推荐写法

SELECT 
  p.created_date,
  p.id AS picture_id,
  JSON_ARRAYAGG(t.id) AS tagIds
FROM pictures p
INNER JOIN picture_tags pt ON p.id = pt.picture_id
INNER JOIN tags t ON pt.tag_id = t.id
WHERE pt.tag_id IN (1, 2)
GROUP BY p.id, p.created_date
ORDER BY p.created_date ASC;

2. PostgreSQL 环境 #

PostgreSQL 原生支持数组类型，直接用 array_agg 聚合标签ID即可：

SELECT 
  p.created_date,
  p.id AS picture_id,
  array_agg(t.id) AS tagIds
FROM pictures p
INNER JOIN picture_tags pt ON p.id = pt.picture_id
INNER JOIN tags t ON pt.tag_id = t.id
WHERE pt.tag_id IN (1, 2)
GROUP BY p.id, p.created_date
ORDER BY p.created_date ASC;

3. SQL Server 环境 #

用 STRING_AGG 拼接标签ID，再包装成数组格式：

SELECT 
  p.created_date,
  p.id AS picture_id,
  CONCAT('[ ', STRING_AGG(t.id, ', '), ' ]') AS tagIds
FROM pictures p
INNER JOIN picture_tags pt ON p.id = pt.picture_id
INNER JOIN tags t ON pt.tag_id = t.id
WHERE pt.tag_id IN (1, 2)
GROUP BY p.id, p.created_date
ORDER BY p.created_date ASC;

补充说明 #

• 原查询从 picture_tags 出发做 LEFT JOIN，这里改成从 pictures 出发做 INNER JOIN——因为你的 WHERE 条件已经过滤了特定标签ID，无需保留无匹配标签的图片；如果需要保留这类图片，把 INNER JOIN 改回 LEFT JOIN 即可。
• 分组字段必须包含所有非聚合的查询字段（比如 p.created_date），避免数据库分组逻辑报错。

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