如何在链接表中获得sql查询的确切结果

Question

我正在尝试获取brand_id与组完全匹配的组。

我尝试过这个解决方案(见fiddle(，但当我试图获得一个品牌id分配给所有组的物品时，它会显示所有三个组。但它应该匹配零，因为它不是完全匹配的。

http://sqlfiddle.com/#！9/a5b16e/2/0

我想知道这个是否有解决方案

SELECT  group_id as groep
FROM    mailgroups a
WHERE   a.brand_id IN (2)
GROUP BY a.group_id
HAVING COUNT(*) = 1

这应该返回第3组

SELECT  group_id as groep
FROM    mailgroups a
WHERE   a.brand_id IN (2, 1)
GROUP BY a.group_id
HAVING COUNT(*) = 2

并且这应该只返回group_id 1

Answer 1

您不需要子查询或联接
在having子句中设置正确的条件，并删除where子句：

SELECT  group_id as groep
FROM    mailgroups 
GROUP BY group_id
HAVING SUM(brand_id IN (2)) = 1 AND SUM(brand_id NOT IN (2)) = 0;
SELECT  group_id as groep
FROM    mailgroups 
GROUP BY group_id
HAVING SUM(brand_id IN (1, 2)) = 2 AND SUM(brand_id NOT IN (1, 2)) = 0;

请参阅演示

Answer 2

您可以使用子查询：

select group_id
from mailgroups 
where group_id in (select group_id from mailgroups where brand_id = 2)
GROUP BY group_id
HAVING COUNT(brand_id) = 1;

---

select group_id
from mailgroups 
where group_id in (select group_id from mailgroups where brand_id in (1,2))
GROUP BY group_id
HAVING COUNT(brand_id) = 2;

与join:的OR

select a.group_id
from mailgroups a 
left join mailgroups b
on a.id = b.id
and b.brand_id in (1,2)
GROUP BY a.group_id
HAVING COUNT(a.brand_id) = 2;

---

select a.group_id
from mailgroups a 
left join mailgroups b
on a.id = b.id
and b.brand_id in (2)
GROUP BY a.group_id
HAVING COUNT(a.brand_id) = 1;