日期:2014-05-18  浏览次数:20631 次

求下列分组统计的语句
A表 left join B表 left join C表
条件为:A.key1 = B.key1
B.key1 = C.key1
B.key2 = C.key2
对应关系为 A表一条记录对应B表一条记录对应C表N条记录(N>2)

现在要根据B表的key1和key2主键来分组统计C表中的某数量c字段的合计值

能不能select出的记录集显示如下字段:

A.a字段,A.b字段,B.key1字段,B.key2字段,C.c数量合计,C.d字段......

即group by的字段外,其他非group by字段也能一起select出来?

------解决方案--------------------
TRY
SQL code
SELECT A.a字段,A.b字段,B.key1字段,B.key2字段,d.数量合计,C.d字段
FROM A
LEFT JOIN B ON A.key1 = B.key1
LEFT JOIN C ON B.key1 = C.key1 AND B.key2 = C.key2
LEFT JOIN
(SELECT key1,key2,SUM(c字段) AS 数量合计 FROM C GROUP BY key1,key2) AS D
ON B.key1 = D.key1 AND B.key2 = D.key2