MySQL按指定关键词筛选出版物，如何返回所有关联关键词？

你的问题根源在于原查询的WHERE keywords.id = 1直接过滤了关联结果集，导致只有该关键词的行被保留，GROUP_CONCAT自然只能拿到这个关键词。我们需要拆分逻辑：先筛选出包含目标关键词的出版物，再单独关联所有关键词来获取完整列表。

方法一：使用IN子查询筛选出版物 #

SELECT p.*, GROUP_CONCAT(k.name SEPARATOR ', ') AS keywords
FROM publications p
LEFT JOIN keyword_relations kr ON kr.pid = p.id
LEFT JOIN keywords k ON kr.kid = k.id
WHERE p.id IN (
    -- 先找出所有包含目标关键词（id=1）的出版物ID
    SELECT kr_sub.pid
    FROM keyword_relations kr_sub
    WHERE kr_sub.kid = 1
)
GROUP BY p.id
ORDER BY p.date DESC;

方法二：使用EXISTS子查询（推荐，大数据量下更高效） #

SELECT p.*, GROUP_CONCAT(k.name SEPARATOR ', ') AS keywords
FROM publications p
LEFT JOIN keyword_relations kr ON kr.pid = p.id
LEFT JOIN keywords k ON kr.kid = k.id
WHERE EXISTS (
    -- 检查当前出版物是否包含目标关键词
    SELECT 1
    FROM keyword_relations kr_sub
    WHERE kr_sub.pid = p.id
      AND kr_sub.kid = 1
)
GROUP BY p.id
ORDER BY p.date DESC;

逻辑说明 #

• 子查询（IN或EXISTS）只负责筛选出包含指定关键词的出版物，不会影响外层对全部关键词的关联。
• 外层的LEFT JOIN会关联该出版物的所有关键词关系，因此GROUP_CONCAT能收集到该出版物的全部关联关键词。
• EXISTS通常比IN效率更高，因为它在找到第一个匹配项后就会停止检索，不需要返回所有匹配的ID列表。

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