关于求和计数及去重的查询

roych

太复杂了：

1. select B2.大品, B2.小品数, B2.副品数,C1.副小品数 from
   
2. (SELECT A.大品, Count(A.小品) AS 小品数, B1.副品数
   
3. FROM (SELECT Sheet1.大品, Sheet1.小品 FROM Sheet1 GROUP BY Sheet1.大品, Sheet1.小品)  AS A
   
4. INNER JOIN
   
5. (SELECT B.大品, Count(B.副品) AS 副品数 FROM (SELECT Sheet1.大品, Sheet1.副品 FROM Sheet1 GROUP BY Sheet1.大品, Sheet1.副品)  AS B GROUP BY B.大品)  AS B1
   
6. ON A.大品=B1.大品
   
7. 
   
8. GROUP BY A.大品, B1.副品数) as B2
   
9. 
   
10. INNER JOIN
    
11. (SELECT C.大品, Count(C.副小品) AS 副小品数 FROM (SELECT Sheet1.大品, Sheet1.副小品 FROM Sheet1 GROUP BY Sheet1.大品, Sheet1.副小品)  AS C GROUP BY C.大品)  AS C1
    
12. ON B2.大品=C1.大品

复制代码

SQL Server里有Count Distinct，会好很多。Access没有这个功能
这还不算，还不能inner join含有3个group by的子查询。所以只能先把A跟B1合起来，再inner join C1。