如何将多个SQL查询统计结果一次显示出来
我们经常会碰到各种分类统计,有时需要将这些统计结果一次显示出来,并计算分类统计占总量的比例,例如:一段时间内每日设备销售总量、台式机销量、笔记本销量等,我的工作中也碰到类似问题,我们需要统计一段时间内邮件收寄总量、自收总量、妥投总量、自投总量和自收自投总量等,以统计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
相关新闻>>
- 发表评论
-
- 最新评论 更多>>