如何查询多对多关联表中同时匹配多个标签的礼品证书记录

要实现同时匹配多个指定标签（AND逻辑：礼品证书必须关联全部传入的标签才会被返回），有两种成熟的实现方案，优先推荐第一种分组聚合方案，适配动态传参场景、性能更稳定。

方案1：分组聚合统计法（推荐） #

核心逻辑：先筛选出关联了任意一个目标标签的证书记录，按证书ID分组后，统计每个证书实际命中的目标标签数量，只有命中数量等于传入标签总个数的证书，才满足全标签匹配的AND要求。
以同时匹配puppy、birthday、discount3个标签为例，基础查询SQL如下：

SELECT gc.*
FROM gift_certificate gc
INNER JOIN gift_certificate__tag joint ON gc.id = joint.certificate_id
INNER JOIN tag t ON joint.tag_id = t.id
-- IN里传入所有需要匹配的标签
WHERE t.name IN ('puppy', 'birthday', 'discount')
GROUP BY gc.id
-- HAVING后的数字等于传入标签的总个数，3个标签就写3
HAVING COUNT(DISTINCT t.id) = 3
ORDER BY gc.id DESC;

如果需要像原单标签查询一样同时返回证书关联的所有标签信息，可以把匹配到的证书ID作为子查询，再关联标签表取数：

SELECT gc.*, t.*
FROM (
    SELECT gc.id
    FROM gift_certificate gc
    INNER JOIN gift_certificate__tag joint ON gc.id = joint.certificate_id
    INNER JOIN tag t ON joint.tag_id = t.id
    WHERE t.name IN ('puppy', 'birthday', 'discount')
    GROUP BY gc.id
    HAVING COUNT(DISTINCT t.id) = 3
) matched_cert
INNER JOIN gift_certificate gc ON matched_cert.id = gc.id
LEFT JOIN gift_certificate__tag joint ON gc.id = joint.certificate_id
LEFT JOIN tag t ON joint.tag_id = t.id
ORDER BY gc.id DESC;

方案2：多轮JOIN匹配法 #

如果标签数量是固定的，可以为每个待匹配标签单独关联一次中间表和标签表，直接通过多表INNER JOIN过滤出同时满足所有标签关联的记录，以匹配2个标签为例：

SELECT DISTINCT gc.*, t.*
FROM gift_certificate gc
-- 关联第一个标签
INNER JOIN gift_certificate__tag j1 ON gc.id = j1.certificate_id
INNER JOIN tag t1 ON j1.tag_id = t1.id AND t1.name = 'puppy'
-- 关联第二个标签
INNER JOIN gift_certificate__tag j2 ON gc.id = j2.certificate_id
INNER JOIN tag t2 ON j2.tag_id = t2.id AND t2.name = 'birthday'
-- 关联取所有标签信息
LEFT JOIN gift_certificate__tag joint ON gc.id = joint.certificate_id
LEFT JOIN tag t ON joint.tag_id = t.id
ORDER BY gc.id DESC;

注意：该方案需要根据传入标签的数量动态拼接JOIN片段，标签数量较多时多表JOIN会导致查询性能明显下降，仅适合标签数量固定且数量少的场景。

原有单标签查询的优化点 #

你写的单标签查询用了LEFT OUTER JOIN关联标签表，但WHERE条件中直接写了tag.name='puppy'，会自动过滤掉所有未匹配到该标签的记录，和直接写INNER JOIN的执行效果完全一致，换成INNER JOIN可以减少不必要的外连接开销，查询效率更高。

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