如何筛选关联标签包含指定全部取值的images表对应父行？

核心思路 #

你原来的语句用IN只能匹配任意一个目标标签，要实现同时包含所有指定标签的筛选，需要通过分组计数来校验每个图片匹配到的标签数量是否等于目标标签总数。

兼容大多数数据库的实现方案 #

SELECT i.image_checksum, i.filename, t.name, t.confidence
FROM images i
INNER JOIN tags t ON t.image = i.image_checksum
WHERE i.image_checksum IN (
    -- 子查询筛选出同时包含所有目标标签的图片ID
    SELECT image
    FROM tags
    WHERE name IN ('dog', 'cat') -- 此处动态替换客户端传入的标签列表
    GROUP BY image
    -- 匹配到的不同标签数量等于目标标签的总数，此处2对应传入标签的数量，动态修改即可
    HAVING COUNT(DISTINCT name) = 2
)
-- 可选：仅保留目标标签的返回结果，删除该行则会返回符合要求的图片的所有标签
AND t.name IN ('dog', 'cat');

逻辑说明 #

1. 子查询先过滤出所有带任意一个目标标签的tag记录
2. 按图片ID分组后，统计每个图片匹配到的不同标签数量，如果数量和传入的标签总数完全相等，说明该图片包含所有指定标签
3. 外层查询关联拿到符合要求的图片和对应标签的详细信息

场景验证 #

针对你给出的示例：一张图片仅带dog标签，另一张仅带cat标签，子查询分组后两个图片的匹配标签计数都是1，不等于2，因此子查询返回空结果，最终查询返回0条，完全符合需求。

优化提示 #

如果你的业务逻辑保证同一个图片不会出现重复的同名标签，可以把COUNT(DISTINCT name)改成COUNT(*)，查询性能会更高。

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