多对多关联表的单查询优化方案咨询

嘿，我来帮你优化这个查询！你的需求是找出两类新闻ID：一类是关联了general标签（tag_id=1）且不关联子标签（tag_id=2、3），另一类是同时关联general和sub tag 1（tag_id=2）且没有其他标签。原查询用了UNION和子查询，确实不够高效也不易扩展，我们可以通过一次分组查询来搞定所有需求。

优化后的查询语句 #

SELECT news_id
FROM news_tags
GROUP BY news_id
HAVING 
  -- 条件2：同时关联tag1和tag2，且没有其他标签
  (SUM(tag_id = 1) = 1 AND SUM(tag_id = 2) = 1 AND COUNT(*) = 2)
  OR
  -- 条件1：关联tag1，且不关联tag2、tag3（允许关联其他非子标签如tag4）
  (SUM(tag_id = 1) = 1 AND SUM(tag_id IN (2, 3)) = 0);

逻辑解释 #

这个查询通过GROUP BY news_id把每个新闻的所有标签聚合在一起，然后在HAVING子句里直接判断两类条件：

• 条件2的判断：
  • SUM(tag_id = 1) = 1：确保该新闻关联了general标签（因为每个标签关联是唯一的，求和等于1表示存在该标签）
  • SUM(tag_id = 2) = 1：确保关联了sub tag 1标签
  • COUNT(*) = 2：确保该新闻只有这两个标签，没有其他额外标签
• 条件1的判断：
  • SUM(tag_id = 1) = 1：确保关联了general标签
  • SUM(tag_id IN (2, 3)) = 0：确保没有关联任何子标签（tag2、tag3），允许关联其他非子标签（比如示例中的tag4）

优势对比 #

1. 性能更优：只需要扫描一次news_tags表，避免了原查询中多次扫描、子查询和UNION带来的额外开销
2. 扩展性强：如果需要新增其他标签组合条件，只需要在HAVING子句里添加新的OR分支即可，不用新增SELECT语句和UNION
3. 逻辑清晰：所有条件判断集中在一处，更容易理解和维护

可选调整 #

如果你的条件1要求仅关联general标签（不能有任何其他标签，包括tag4），只需要把条件1的判断改成：

(SUM(tag_id = 1) = 1 AND COUNT(*) = 1)

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