SQL以'= ALL'格式获取行

I have simple many-to-many relation in table Product_Category (MSSQL 2008 r2):

表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

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

如何选择ProductId，条件为:CategoryId = 200, CategoryId = 300, CategoryId = 400?

Query example (sql below doesn't work):

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

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

I expect result: ProductId = 2

我期望的结果是:ProductId = 2

7 个解决方案

#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

#2

Update: It is still ugly but it does work:

更新:它仍然很丑，但它确实有效:

SELECT DISTINCT master.ProductId
FROM #Product_Category master
JOIN (
    SELECT ProductId, 
           cat200 = max(case when CategoryId=200 then 1 else 0 end),
           cat300 = max(case when CategoryId=300 then 1 else 0 end),
           cat400 = max(case when CategoryId=400 then 1 else 0 end)
   FROM #Product_Category
   GROUP BY ProductId
) sub ON sub.ProductId = master.ProductId
WHERE cat200=1
  and cat300=1
  AND cat400=1

#3

try this

试试这个

SELECT a.ProductId 
    FROM Product_Category as a, 
        Product_Category as b, 
        Product_Category as c 
        WHERE a.CategoryId = 200 
            And b.`CategoryId` = 300 
            And c.`CategoryId` = 400 
            And a.`ProductId` = b.`ProductId` 
            And b.`ProductId` = c.`ProductId`

for more like 500 and 600

大约500到600美元

SELECT a.ProductId 
    FROM Product_Category as a, 
        Product_Category as b, 
        Product_Category as c,
        Product_Category as d,
        Product_Category as e,
        WHERE a.CategoryId = 200 
            And b.`CategoryId` = 300 
            And c.`CategoryId` = 400 
            And d.`CategoryId` = 500 
            And e.`CategoryId` = 600 
            And a.`ProductId` = b.`ProductId` 
            And b.`ProductId` = c.`ProductId`
            And c.`ProductId` = d.`ProductId`
            And d.`ProductId` = e.`ProductId`

check live demo http://sqlfiddle.com/#!2/8965e/1/0

检查现场演示http://sqlfiddle.com/ ! 2/8965e / 1/0

#4

Here I am using a CTE, but you could use a table variable or something different for the category_filter.

这里我使用的是CTE，但是对于category_filter，您可以使用一个表变量或者其他的东西。

with category_filter as (
    select * from (values (200), (300), (400)) as v(id)
)
select distinct ProductId
from #Product_Category
join category_filter
    on (#Product_Category.CategoryId = category_filter.id)
group by ProductId
having COUNT(distinct CategoryId) = (select COUNT(*) from category_filter)

#5

Also ugly solution :)

丑陋的解决方案:)

WITH category_filter1(CategoryId) AS (
    SELECT * FROM (VALUES (200), (300), (400)) tmp1(tmp2)
)
SELECT p.ProductId
FROM (
    SELECT ProductId,
        CASE WHEN CategoryId IN (SELECT CategoryId FROM category_filter1) THEN 1 ELSE 0 END f
    FROM #Product_Category
    ) p
GROUP BY p.ProductId, p.f
HAVING COUNT(*) = (SELECT COUNT(*) FROM category_filter1);

#6

You can use Values as Table Source and check them in WHERE clause with NOT EXISTS and EXCEPT operators

您可以使用值作为表源，并在不存在的WHERE子句中检查它们，除了操作符。

SELECT *
FROM #Product_Category p
WHERE NOT EXISTS (                                                        
                  SELECT Match
                  FROM (VALUES(200), 
                              (300),
                              (400))
                  x(Match)
                  EXCEPT
                  SELECT CategoryId
                  FROM #Product_Category p2
                  WHERE p.ProductID = p2.ProductID
                  )  

Demo on SQLFiddle

演示在SQLFiddle

#7

WITH L AS (
SELECT *
  FROM (VALUES (200),(300),(400)) AS T(CategoryId)
)
SELECT ProductId
  FROM Product_Category P
 INNER JOIN L
    ON L.CategoryId = P.CategoryId
 GROUP BY ProductId
HAVING COUNT(1) = (SELECT Count(1) FROM L)
;

The WITH disappears if you are planning to use TVP.

如果你打算使用TVP, WITH就消失了。