SQL以'= ALL'样式获取行 [英] SQL get rows in '= ALL' style

问题描述

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

I have simple many-to-many relation in table 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

如何选择条件为:类别ID = 200 和类别ID = 300 和类别ID = 400的ProductId?

How can I select ProductId with condition: CategoryId = 200 and CategoryId = 300 and CategoryId = 400?

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

Query example (sql below doesn't work):

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

我期望结果:ProductId = 2

推荐答案

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

这篇关于SQL以'= ALL'样式获取行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！