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

fwfewfds

各位老师们好：
       在此请教个问题，附件中左边有四列数据，需用access查询达到右边的效果。关键在于去重和计数的问题，谢谢各位了。

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。

fwfewfds

这个问题不能用access解决吗？

tmtony

用代码可以实现，但用一条SQL查询语句没想好如何实现

todaynew

select a.大品,a.小品,b.副品,c.副小品 from
(select t.大品,count(t.小品) as 小品
from (select distinct 大品,小品 from tbname) as t group by t.大品) as a
inner join
(select t.大品,count(t.副品) as 副品
from (select distinct 大品,副品 from tbname) as t group by t.大品) as b
on a.大品=b.大品
inner join
(select t.大品,count(t.副小品) as 副小品
from (select distinct 大品,副小品 from tbname) as t group by t.大品) as c
on a.大品=c.大品

另：这个表设计有问题，应该设计成BOM表。目前的表实际是BOM表递归查询的结果集。实际上是可以通过BOM表做递归查询时直接计算出数量的。你给弄球反了。

fwfewfds

谢谢三位老师的热心解答。

fwfewfds

roych 发表于 2015-11-18 10:34
太复杂了：

SQL Server里有Count Distinct，会好很多。Access没有这个功能

感谢roych老师的解答，完美运行得出答案。

fwfewfds

todaynew 发表于 2015-11-18 16:07
select a.大品,a.小品,b.副品,c.副小品 from
(select t.大品,count(t.小品) as 小品
from (select disti ...

todaynew老师的解答执行有点问题，同样感谢。