制作SQL二维表

 
如果需要类似的如上图的二维表，可以用到left outer join ...on ...
具体如下
通过一下语句，可以查出相应单位的电视类别的工单数：

select l.id,l.report_location,count(*) as dianshi from (select bpi.id,bpi.report_location from itsm_bp_incident bpi,itsm_bp_category bpc where bpi.CATEGORY_ID=bpc.ID AND bpc.name='电视' ) l , itsm_bp_instance bps where l.id= bps.ID and bps.CREATED_ON BETWEEN '2013-02-01 00:00:00' and '2013-06-28 00:00:00'  group by l.report_location
查询结果类似如下：
 
通过 left outer join 可以加入电话的资料，具体如下：
select a.report_location as '单位',IFNULL(a.dianshi,0) as '电视',IFNULL(b.dianhua,0) as '电话' from (select l.id,l.report_location,count(*) as dianshi from (select bpi.id,bpi.report_location from itsm_bp_incident bpi,itsm_bp_category bpc where bpi.CATEGORY_ID=bpc.ID AND bpc.name='电视' ) l , itsm_bp_instance bps where l.id= bps.ID and bps.CREATED_ON BETWEEN '2013-02-01 00:00:00' and '2013-06-28 00:00:00' group by l.report_location) a  LEFT OUTER JOIN
(select l.id,l.report_location,count(*) as dianhua from (select bpi.id,bpi.report_location from itsm_bp_incident bpi,itsm_bp_category bpc where bpi.CATEGORY_ID=bpc.ID AND bpc.name='电话' ) l , itsm_bp_instance bps where l.id= bps.ID and bps.CREATED_ON BETWEEN '2013-02-01 00:00:00' and '2013-06-28 00:00:00'  group by l.report_location) b on a.report_location=b.report_location 
查询结果如下： 

通过类似方法，可以加入更多类别，但是需要对应的是category_id，必须共有的，且作为on的参数来left outer join