需要帮助构建单个表的 SQL 查询

Question

我有一个包含以下字段的表：

ID 
brandID
productID

我想知道有多少个不同的brandID在此表中至少有一条记录同时包含productID 1和productID 2。例如：

ID|brandID|productID
1 | 1     | 1
2 | 1     | 2
3 | 1     | 2
4 | 2     | 2
5 | 3     | 1
6 | 3     | 1

在上面的示例表中，只有brandID 1有一行包含productID 1 和productID 2。brandID 2 和3 仅包含一种产品。

如何构建查询来获取同时包含 ProductID 1 和 2 的不同品牌 ID 的计数？

Answer 1

select COUNT(*)
from (
    select brandID
    from MyTable
    where productID in (1, 2)
    group by brandID
    having count(distinct productID) = 2
) a

Answer 2

如果你的表名为 t，它应该是这样的：

select count(distinct t1.brandID)
from t t1, t t2
where t1.brandID = t2.brandID and t1.productID = 1 and t2.productID = 2

Answer 3

使用子选择通过 productID=1 获取不同的 brandID，然后获取计数：

SELECT COUNT(DISTINCT(brandID)) FROM tbl WHERE BrandID IN （从表中选择不同的品牌ID，其中产品ID=1）和产品ID=2；

Answer 4

select distinct brandid, count(1) from sampletable 
 where productid in (1, 2) 
 group by brandid having count(1) > 1

Answer 5

对于 SQL Server 2005 及更高版本：

DECLARE @t TABLE(ID INT, brandID INT, productID INT)
INSERT INTO @t
SELECT 1, 1, 1 UNION ALL 
SELECT 2, 1, 2 UNION ALL 
SELECT 3, 1, 2 UNION ALL 
SELECT 4, 2, 2 UNION ALL 
SELECT 5, 3, 1 UNION ALL 
SELECT 6, 3, 2 UNION ALL 
SELECT 7, 3, 4 UNION ALL 
SELECT 8, 3, 5

SELECT * FROM @t    

;WITH cte
AS
(
    SELECT  ROW_NUMBER() OVER(PARTITION BY brandID ORDER BY productID) AS RN, *
    FROM    @t
    WHERE   productID IN (1,2)
)
SELECT  DISTINCT brandID
FROM    cte
WHERE   RN > 1

Answer 6

这就是我要做的：

select distinct brandid from t
where brandid in (select distinct brandid from t where productid = 1)
and brandid in  (select distinct brandid from t where productid = 2)