MySQL的减号? [英] MINUS in MySQL?

问题描述

我有topic(id *)和tags(id *，name)以及一个链接表topic_tags(topicFk *，tagFk *).

I have topics(id*) and tags(id*,name) and a linking table topic_tags(topicFk*,tagFk*).

现在，我想选择每个主题，每个主题都具有所有好的标签(a，b，c)，但没有一个坏标签(d，e，f).

Now I want to select every single topic, that has all of the good tags (a,b,c) but none of the bad tags (d,e,f).

我该怎么做?

推荐答案

我自己使用Pauls和Bills想法的解决方案.

My own solution using Pauls and Bills ideas.

这个想法是将具有良好标签的主题进行内部联接(以抛出没有良好标签的主题)，然后为每个主题计数唯一的标签(以验证是否存在所有良好的标签).

The idea is to inner join topics with good tags (to throw out topics with no good tags) and then count the unique tags for each topic (to verify that all the good tags are present).

与此同时，带有错误标签的外部联接不应具有单个匹配项(所有字段均为NULL).

At the same time an outer join with bad tags should have not a single match (all fields are NULL).

SELECT topics.id
FROM topics
  INNER JOIN topic_tags topic_ptags
    ON topics.id = topic_ptags.topicFk
  INNER JOIN tags ptags
    ON topic_ptags.tagFk = ptags.id
      AND ptags.name IN ('a','b','c')
  LEFT JOIN topic_tags topic_ntags
    ON topics.id = topic_ntags.topicFk
  LEFT JOIN tags ntags
    ON topic_ntags.tagFk = ntags.id
      AND ntags.name IN ('d','e','f')
GROUP BY topics.id
HAVING count(DISTINCT ptags.id) = 3
  AND count(ntags.id) = 0

这篇关于MySQL的减号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！