SQL中使用group by 及left outer join...on...

在做多维报表的时候，统计数量的时候，需要用到group by和left join等语法，使用过程中需要注意几点，以项目中一个小报表为例说明下：

（涉及到小区地址，用MD5加密了）

 

 其中第三列与第四列是通过left join来实现的，当然了，前面两项（单位和小区）则是共有的，也就是on的参数值，注意这里的on是两个参数，而不仅仅是一个
首先获取总数（这里获取总数，是因为总数里包含的小区地址是最全面的，做左外连接最左端不会有遗漏） 

 select z.REPORT_LOCATION reportLocation,z.LOCALNAME localName,z.sum from (select b.REPORT_LOCATION,flt.LOCALNAME,count(flt.LOCALNAME) as sum,b.address,b.CREATED_ON from (select ibc.REPORT_LOCATION,a.ADDRESS,ibc.CATEGORY_ID,a.CREATED_ON from (select ibi.id,iu.ADDRESS,ibi.CREATED_ON from itsm_bp_instance ibi,itsm_user iu where ibi.APPLICANT_ID=iu.ID) as a,itsm_bp_incident ibc where ibc.id=a.id) b,itsm_bp_category bpc,ff_code_localname flt where b.CATEGORY_ID=bpc.ID and b.address like concat(flt.LOCALNAME,'%') and b.CREATED_ON between '2013-01-01 00:00:00' and '2013-07-27 00:00:00' and b.REPORT_LOCATION in('1','2','3','4','5') group by b.REPORT_LOCATION, flt.LOCALNAME ORDER BY b.REPORT_LOCATION,flt.LOCALNAME) z 

由于牵扯到的表较多，查询比较费事，是目前想到的解决方案，当然在java中直接通过left join五张表获取出来各个category，然后加工数据也是可以的
继续left join on来加入dianshi的数量

select z.REPORT_LOCATION  reportLocation,z.LOCALNAME localName,z.sum ,IFNULL(f.dianshi,0) dianshi
-- （,IFNULL(g.dianhua,0) dianhua,IFNULL(h.wangluo,0) wangluo,IFNULL(i.donghuan,0) donghuan,IFNULL(j.zhuji,0) zhuji,IFNULL(k.wangluoshebei,0) wangluoshebei ）
from (select b.REPORT_LOCATION,flt.LOCALNAME,count(flt.LOCALNAME) as sum,b.address,b.CREATED_ON from (select ibc.REPORT_LOCATION,a.ADDRESS,ibc.CATEGORY_ID,a.CREATED_ON from (select ibi.id,iu.ADDRESS,ibi.CREATED_ON from itsm_bp_instance ibi,itsm_user iu where ibi.APPLICANT_ID=iu.ID) as a,itsm_bp_incident ibc where ibc.id=a.id) b,itsm_bp_category bpc,ff_code_localname flt where b.CATEGORY_ID=bpc.ID and b.address like concat(flt.LOCALNAME,'%') and b.CREATED_ON between '2013-01-01 00:00:00' and '2013-07-27 00:00:00' and b.REPORT_LOCATION in('1','2','3','4','5') group by b.REPORT_LOCATION, flt.LOCALNAME ORDER BY b.REPORT_LOCATION,flt.LOCALNAME) z LEFT OUTER JOIN
 (select b.REPORT_LOCATION,flt.LOCALNAME,count(flt.LOCALNAME) as dianshi,b.address,b.CREATED_ON from (select ibc.REPORT_LOCATION,a.ADDRESS,ibc.CATEGORY_ID,a.CREATED_ON from (select ibi.id,iu.ADDRESS,ibi.CREATED_ON from itsm_bp_instance ibi,itsm_user iu where ibi.APPLICANT_ID=iu.ID) as a,itsm_bp_incident ibc where ibc.id=a.id) b,itsm_bp_category bpc,ff_code_localname flt where b.CATEGORY_ID=bpc.ID and bpc.`NAME`='电视' and b.address like concat(flt.LOCALNAME,'%') and b.CREATED_ON between '2013-01-01 00:00:00' and '2013-07-27 00:00:00' and b.REPORT_LOCATION in('1','2','3','4','5') group by b.REPORT_LOCATION,flt.LOCALNAME) f on (f.LOCALNAME=z.LOCALNAME and f.REPORT_LOCATION=z.REPORT_LOCATION)

通过类似的左外连可以做出上述报表类型，以上需要注意两点：
1.group by 需要多级，也即先通过单位，再通过小区来成组，才可以计算出正确的数量
2.left outer join on中on的参数需要注意是两个参数，用  on( ...and ...  ),这种情况是由于left join on中由两个参数共同确定该组的，不能缺少参数

 -------------------------------------------------------------------------------------------

以上记录作为自己的工作中的解决方案，总觉得应该还有更简便的方法，且待深入理解SQL后再做修改，此处做个备份也好