将集合理论的思想转化为SQL

Question

假设我有一个下面的db表:

╔═════════════════╦════════════╗
║ ADVERTISEMENTID ║ CATEGORYID ║
╠═════════════════╬════════════╣
║               1 ║ A          ║
║               1 ║ C          ║
║               2 ║ A          ║
║               2 ║ B          ║
║               3 ║ A          ║
╚═════════════════╩════════════╝

给定一个作为参数传递的类别列表，例如 A, C 我想只查找那些只属于这两个类别的广告，在上面的情况下，只有广告1将匹配。

谁能帮我翻译成SQL?

Answer 1

select advertismentid
from the_table
where categoryid in ('A', 'C')
group by  advertismentid
having count(*) = 2;

SQLFiddle: http://sqlfiddle.com/# !12个/b94d6/1

这假定同一个categoryid不能被多次分配给同一个advertisentid。它还将包括有A、C和其他类别的广告。

如果你想要那些完全包含类别A和C的广告，你需要排除那些多于类别的广告:

select advertismentid
from the_table
where categoryid in ('A', 'C')
group by  advertismentid
having count(*) = 2;
intersect
select advertismentid
from the_table
group by advertismentid
having count(*) = 2;

SQLFiddle: http://sqlfiddle.com/# !12/8901c/4
SQLFiddle还有另一个解决方案，使用except代替intersect

如果您的DBMS是有限的，您不能使用except或intersect，您可以使用这个替代方案:

select t1.advertismentid
from the_table t1
where t1.categoryid in ('A', 'C')
group by t1.advertismentid
having count(*) = 2
and count(*) = (select count(*) 
                from the_table t2
                where t2.advertismentid = t1.advertismentid)

Answer 2

SELECT DISTINCT advertisementid 
FROM tft t1
WHERE t1.categoryid IN ('A','C')
AND EXISTS (
  SELECT * FROM tft t2
  WHERE t2.advertisementid = t1.advertisementid
  AND t2.categoryid IN ('A','C')
  AND t2.categoryid <> t1.categoryid
  );

Answer 3

似乎我来晚了，但这是我的解决方案:

SELECT advertisement
FROM   advertisement_childcare_types t1
LEFT JOIN (
       SELECT childcare_types ct
       FROM   table_childcare_types tct
       WHERE  childcare_types IN (0, 1, 3)
          ) AS mytypes
ON     t1.childcare_types = mytypes.ct
GROUP BY advertisement
HAVING SUM(IF(mytypes.ct IS NULL, -1, 1)) = 3;

您可以使用修改后的版本在sqlfiddle中进行测试:

SELECT advertisement
FROM   advertisement_childcare_types t1
LEFT JOIN (SELECT 0 as ct UNION SELECT 1 UNION SELECT 3) AS mytypes
ON     t1.childcare_types = mytypes.ct
GROUP BY advertisement
HAVING SUM(IF(mytypes.ct IS NULL, -1, 1)) = 3;