|
本帖最后由 roych 于 2015-11-18 10:37 编辑
太复杂了:
- select B2.大品, B2.小品数, B2.副品数,C1.副小品数 from
- (SELECT A.大品, Count(A.小品) AS 小品数, B1.副品数
- FROM (SELECT Sheet1.大品, Sheet1.小品 FROM Sheet1 GROUP BY Sheet1.大品, Sheet1.小品) AS A
- INNER JOIN
- (SELECT B.大品, Count(B.副品) AS 副品数 FROM (SELECT Sheet1.大品, Sheet1.副品 FROM Sheet1 GROUP BY Sheet1.大品, Sheet1.副品) AS B GROUP BY B.大品) AS B1
- ON A.大品=B1.大品
- GROUP BY A.大品, B1.副品数) as B2
- INNER JOIN
- (SELECT C.大品, Count(C.副小品) AS 副小品数 FROM (SELECT Sheet1.大品, Sheet1.副小品 FROM Sheet1 GROUP BY Sheet1.大品, Sheet1.副小品) AS C GROUP BY C.大品) AS C1
- ON B2.大品=C1.大品
复制代码
SQL Server里有Count Distinct,会好很多。Access没有这个功能
这还不算,还不能inner join含有3个group by的子查询。所以只能先把A跟B1合起来,再inner join C1。
|
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?注册
x
|