我们经常会碰到各种分类统计，有时需要将这些统计结果一次显示出来，并计算分类统计占总量的比例，例如：一段时间内每日设备销售总量、台式机销量、笔记本销量等，我的工作中也碰到类似问题，我们需要统计一段时间内邮件收寄总量、自收总量、妥投总量、自投总量和自收自投总量等，以统计2011年10月16-22日合肥到安庆邮件为例，其查询语句如下：

--收寄总量：

select a.clct_date,count(*) from tb_evt_mail_clct a

where a.clct_date>=to_date('20111016','yyyymmdd') and a.clct_date<=to_date('20111022','yyyymmdd')

and exists (select 1 from tb_jg b where b.zj_code=a.clct_bureau_org_code and b.city ='合肥市')

and exists (select 1 from tb_jg d where d.xs_code=substr(a.rcv_area,1,4) and d.city ='安庆市')

group by a.clct_date order by a.clct_date;

--自收总量：

select a.clct_date,count(*) from tb_evt_mail_clct a

where a.clct_date>=to_date('20111016','yyyymmdd') and a.clct_date<=to_date('20111022','yyyymmdd')

and exists (select 1 from sncn_zd_jg b where b.zj_code=a.clct_bureau_org_code and b.city ='合肥市')

and exists (select 1 from tb_jg d where d.xs_code=substr(a.rcv_area,1,4) and d.city ='安庆市')

group by a.clct_date order by a.clct_date;

--妥投总量：

select a.clct_date,count(*) from tb_evt_mail_clct a,tb_evt_dlv c

where a.clct_date>=to_date('20111016','yyyymmdd') and a.clct_date<=to_date('20111022','yyyymmdd')

and a.mail_num=c.mail_num and c.dlv_sts_code='I'

and exists (select 1 from tb_jg b where b.zj_code=a.clct_bureau_org_code and b.city ='合肥市')

and exists (select 1 from tb_jg d where d.zj_code=c.dlv_bureau_org_code and d.city ='安庆市')

group by a.clct_date order by a.clct_date;

--自投总量：

select a.clct_date,count(*) from tb_evt_mail_clct a,tb_evt_dlv c

where a.clct_date>=to_date('20111016','yyyymmdd') and a.clct_date<=to_date('20111022','yyyymmdd')

and a.mail_num=c.mail_num and c.dlv_sts_code='I'

and exists (select 1 from tb_jg b where b.zj_code=a.clct_bureau_org_code and b.city ='合肥市')

and exists (select 1 from sncn_zd_jg d where d.zj_code=c.dlv_bureau_org_code and d.city ='安庆市')

group by a.clct_date order by a.clct_date;

--自收自投总量：

select a.clct_date,count(*) from tb_evt_mail_clct a,tb_evt_dlv c

where a.clct_date>=to_date('20111016','yyyymmdd') and a.clct_date<=to_date('20111022','yyyymmdd')

and a.mail_num=c.mail_num and c.dlv_sts_code='I'

and exists (select 1 from sncn_zd_jg b where b.zj_code=a.clct_bureau_org_code and b.city ='合肥市')

and exists (select 1 from sncn_zd_jg d where d.zj_code=c.dlv_bureau_org_code and d.city ='安庆市')

group by a.clct_date order by a.clct_date;

将每个查询结果看着是一个表，使用join关键字将所有的查询连接起来，就可以一次显示所有查询结果，语句如下：

select aa.rq,aa.sjzl,bb.zszl,cc.ttzl,dd.ztzl,ee.zszt from

(select a.clct_date rq,count(*) sjzl from tb_evt_mail_clct a

where a.clct_date>=to_date('20111016','yyyymmdd') and a.clct_date<=to_date('20111022','yyyymmdd')

and exists (select 1 from tb_jg b where b.zj_code=a.clct_bureau_org_code and b.city ='合肥市')

and exists (select 1 from tb_jg d where d.xs_code=substr(a.rcv_area,1,4) and d.city ='安庆市')

group by a.clct_date order by a.clct_date) aa

left join

(select a.clct_date rq,count(*) zszl from tb_evt_mail_clct a

where a.clct_date>=to_date('20111016','yyyymmdd') and a.clct_date<=to_date('20111022','yyyymmdd')

and exists (select 1 from sncn_zd_jg b where b.zj_code=a.clct_bureau_org_code and b.city ='合肥市')

and exists (select 1 from tb_jg d where d.xs_code=substr(a.rcv_area,1,4) and d.city ='安庆市')

group by a.clct_date order by a.clct_date) bb

on aa.rq=bb.rq

left join

(select a.clct_date rq,count(*) ttzl from tb_evt_mail_clct a,tb_evt_dlv c

where a.clct_date>=to_date('20111016','yyyymmdd') and a.clct_date<=to_date('20111022','yyyymmdd')

and a.mail_num=c.mail_num and c.dlv_sts_code='I'