多表关联SQL查询咨询：单书标签及指定状态书籍带标签查询

首先，你第一个问题的查询思路完全没问题，我稍微调整下字段引用的写法，避免可能的表字段歧义，优化后的语句如下：

SELECT tags.id, tags.name 
FROM books_tags 
INNER JOIN tags ON books_tags.tag_id = tags.id 
WHERE books_tags.book_id = 1;

接下来重点解决第二个问题：要获取所有status=true的书籍标题，同时附带它们的标签数组，核心是用分组聚合把每本书的多个标签合并成一个集合。不同数据库的聚合函数略有差异，下面分两种常见场景说明：

MySQL 实现方案 #

用GROUP_CONCAT函数把标签转为JSON对象后拼接成类似数组的结构：

SELECT 
    b.title,
    -- 将每个标签转为JSON对象，再拼接成模拟数组的字符串
    GROUP_CONCAT(JSON_OBJECT('id', t.id, 'name', t.name) SEPARATOR ',') AS tags
FROM 
    books b
-- 用LEFT JOIN保证无标签的书籍也能被返回
LEFT JOIN 
    books_tags bt ON b.id = bt.book_id
LEFT JOIN 
    tags t ON bt.tag_id = t.id
WHERE 
    b.status = true
-- 按书籍唯一标识分组，确保每本书只返回一行
GROUP BY 
    b.id, b.title;

如果只需要标签名称的集合，可以简化为GROUP_CONCAT(t.name) AS tags，结果会是逗号分隔的名称字符串。

PostgreSQL 实现方案 #

PostgreSQL支持原生数组类型，推荐用ARRAY_AGG直接生成数组：

SELECT 
    b.title,
    -- 生成包含标签JSON对象的原生数组
    ARRAY_AGG(json_build_object('id', t.id, 'name', t.name)) AS tags
FROM 
    books b
LEFT JOIN 
    books_tags bt ON b.id = bt.book_id
LEFT JOIN 
    tags t ON bt.tag_id = t.id
WHERE 
    b.status = true
GROUP BY 
    b.id, b.title;

如果只需要标签名称数组，可改为ARRAY_AGG(t.name) AS tags，结果会是PostgreSQL原生的文本数组类型。

关键注意事项 #

• 必须用LEFT JOIN而非INNER JOIN：否则没有标签的status=true书籍会被过滤掉，不符合你提到的“每本书可无标签”的设定。
• GROUP BY要包含书籍的id和title：用唯一标识id分组能避免因书籍重名导致的错误合并，保证结果准确性。

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