如何在SQL中匹配任一标签时获取照片的全部关联标签？

要实现“匹配一个标签就获取对应照片的所有标签”，核心是先锁定包含目标标签的照片ID，再关联获取该照片的全部标签，而不是直接过滤标签名称。以下是两种可行的SQL写法：

方法一：子查询筛选目标照片 #

SELECT p.id AS photo_id, t.name AS tag_name
FROM photos p
JOIN photos_tags pt ON p.id = pt.photo_id
JOIN tags t ON pt.tag_id = t.id
WHERE p.id IN (
    -- 先找出所有标记了'dog'的照片ID
    SELECT pt_inner.photo_id
    FROM photos_tags pt_inner
    JOIN tags t_inner ON pt_inner.tag_id = t_inner.id
    WHERE t_inner.name = 'dog'
);

内层子查询先定位到所有包含'dog'标签的照片ID，外层查询通过这些ID关联出对应照片的全部标签，就能返回该照片的dog、cat、bird三个标签。

方法二：自关联匹配目标照片 #

SELECT p.id AS photo_id, t.name AS tag_name
FROM photos p
JOIN photos_tags pt ON p.id = pt.photo_id
JOIN tags t ON pt.tag_id = t.id
-- 关联到包含目标标签的记录，锁定照片
JOIN photos_tags pt_match ON p.id = pt_match.photo_id
JOIN tags t_match ON pt_match.tag_id = t_match.id
WHERE t_match.name = 'dog';

通过自关联photos_tags和tags表，先匹配到包含'dog'标签的照片，再直接关联出该照片的所有标签。如果存在重复标签的情况，可以给SELECT加上DISTINCT去重。

注：如果你的原SQL直接写WHERE t.name = 'dog'，只会过滤出该标签的行，而无法拿到同一张照片的其他标签，所以必须先锁定照片再拉取所有标签。

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