我有一个多对多索引表，并且我想对其进行包含/排除类型查询。

fid实际上是一个整数索引，但此处以字母表示，以便于理解。这是一个示例表：

药片

eid | fid
----+----
1   | A
1   | B
1   | C
2   | B
2   | C
3   | A
3   | C
4   | A
4   | B
5   | B

这是我想要的一些示例查询。

• 哪些节日叫B，而不是A？ (开斋节2和5)
• 哪些节日有C，而没有A？ (开斋节2)

我似乎无法找出一个可以做到这一点的查询。

我已经尝试过像这样的自我加入：

select * 
from t as t1 
join t as t2 
where t1.eid=t2.eid 
  and t1.fid!=t2.fid 
  and t1.fid=B and t2.fid!=A

那将行不通，因为它仍将返回eid = 1和fid = C的行。

我清楚我想要什么吗？

解决方案

使用集合减法

Select eid from t where fid = 'B' 
EXCEPT
select eid from t where fid = 'A'

我们可以使用子选择

从t处选择eid，其中fid ='C'，而不是eid(从t中选择eid，其中fid ='A')

这是一个查询1的示例(2个工作原理大致相同)

select t1.eid
  from t t1
 where t1.fid  = 'B'
   and not exists
       (select 1
          from t t2
         where t2.eid = t1.eid
           and t2.fid  = 'A')

如Nigel和Mike所述，MySQL 5.0支持在哪里存在/在哪里不存在。

具有直接联接的版本可能比使用EXISTS更快：

Select  t1.eid
From    #test t1
        left join (
            Select  eid
            From    #test t2 
            Where   fid = 'A'
            Group by eid
        ) t2 on t2.eid = t1.eid 
Where   t1.fid = 'B'
        and t2.eid is null

不用子查询就可以做到这一点：

SELECT DISTINCT t1.eid
FROM table1 AS t1
  LEFT JOIN table1 AS t2 ON (t1.eid = t2.eid AND t2.fid = 'A')
WHERE t2.eid IS NULL
  AND t1.fid = 'B';

要进行第二个示例搜索，只需将值" B"更改为" C"。

查看MINUS运算符。它的工作方式与UNION相似，不同之处在于它会减去UNION添加的位置。前面带有单词" EXCEPT"的答案可能是同一件事的不同关键字。

这是一个未经测试的答案：

select eid 
from t
where fid = 'A'
minus
select eid
from t
where fid = 'B'