本文介绍了SQL以'= ALL'样式获取行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在表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'样式获取行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！