22,210
社区成员
发帖
与我相关
我的任务
分享
SELECT classid,COUNT(ProductID) AS 数量 FROM View_mf_ProductList WHERE (AttID = 1) GROUP BY ClassID
A1 $select distinct ClassID,ProductID,AttID from tb where AttID in(1,2)
A2 =A1.group(ClassID;~.group(ProductID).count(~.len()==2):数量)
很好理解,也可以嵌入应用程序中。--方法1
SELECT T1.classid,ISNULL(T2.数量,0)数量
FROM(
SELECT ClassID FROM View_mf_ProductList GROUP BY classid
)T1 LEFT JOIN(
SELECT
ClassID,COUNT(DISTINCT ProductID)AS 数量
FROM
View_mf_ProductList
WHERE AttID IN(1,2)
GROUP BY ALL ClassID
HAVING COUNT(DISTINCT ProductID)=2
)T2 ON T1.ClassID=T2.ClassID
--方法2
SELECT
ClassID,COUNT(DISTINCT ProductID)AS 数量
FROM
View_mf_ProductList T1
WHERE EXISTS(SELECT 1 FROM View_mf_ProductList T2
WHERE T1.ProductID=T2.ProductID AND T2.AttID=1)
AND EXISTS(SELECT 1 FROM View_mf_ProductList T2
WHERE T1.ProductID=T2.ProductID AND T2.AttID=2)
GROUP BY ALL ClassID
如果不要显示14数量为0的话,方法一可以简写了--方法1
SELECT
ClassID,COUNT(DISTINCT ProductID)AS 数量
FROM
View_mf_ProductList
WHERE AttID IN(1,2)
GROUP BY ALL ClassID
HAVING COUNT(DISTINCT ProductID)=2
--方法2
SELECT
ClassID,COUNT(DISTINCT ProductID)AS 数量
FROM
View_mf_ProductList T1
WHERE EXISTS(SELECT 1 FROM View_mf_ProductList T2
WHERE T1.ProductID=T2.ProductID AND T2.AttID=1)
AND EXISTS(SELECT 1 FROM View_mf_ProductList T2
WHERE T1.ProductID=T2.ProductID AND T2.AttID=2)
GROUP BY ClassID