SQL 获取 '= ALL' 样式的行

Question

我在表 Product_Category (MSSQL 2008 r2) 中有简单的多对多关系:

CREATE TABLE #Product_Category (ProductId int, CategoryId int);
go
INSERT INTO #Product_Category (ProductId, CategoryId)
VALUES (1, 200);
go
INSERT INTO #Product_Category (ProductId, CategoryId)
VALUES (2, 200);
go
INSERT INTO #Product_Category (ProductId, CategoryId)
VALUES (2, 400);
go
INSERT INTO #Product_Category (ProductId, CategoryId)
VALUES (3, 300);
go
INSERT INTO #Product_Category (ProductId, CategoryId)
VALUES (2, 300);
go
DROP TABLE #Product_Category

如何选择具有条件的 ProductId:CategoryId = 200 and CategoryId = 300 and CategoryId = 400？

查询示例(下面的sql不起作用):

SELECT ProductId FROM #Product_Category
WHERE CategoryId = ALL (select 200 union select 300 union select 400)

我期望结果:ProductId = 2

Answer 1

select PC.ProductId
from #Product_Category as PC
where PC.CategoryId in (200, 300, 400)
group by PC.ProductId
having count(distinct PC.CategoryId) = 3