把多个SQL查询统计结果一次显示的方法详解

发布时间:2021-01-18编辑:脚本学堂
本文介绍下,实现把多个sql查询统计结果一次性显示出来的方法,有需要的朋友可以参考下。

有时需要将这些统计结果一次显示出来,并计算分类统计占总量的比例。
例如:
一段时间内每日设备销售总量、台式机销量、笔记本销量等。

来看一个例子,需要统计一段时间内邮件收寄总量、自收总量、妥投总量、自投总量和自收自投总量等,以统计2011年10月16-22日合肥和芜湖到安庆邮件为例。

查询语句:
 

复制代码 代码示例:

--收寄总量:
select a.clct_date rq,b.city sjj,d.city jdj,count(*) sjzl from tb_evt_mail_clct a,tb_jg b,(select distinct city,xs_code from sncn_zd_jg) d
 where a.clct_date>=to_date('20111016','yyyymmdd') and a.clct_date<=to_date('20111022','yyyymmdd')
  and b.zj_code=a.clct_bureau_org_code and d.xs_code=substr(a.rcv_area,1,4)
  and b.city in ( '合肥市','芜湖市' )and d.city in ('安庆市')
  group by a.clct_date,b.city,d.city order by a.clct_date,b.city,d.city;

--自收总量:
select a.clct_date rq,b.city sjj,d.city jdj,count(*) zszl from tb_evt_mail_clct a,sncn_zd_jg b,(select distinct city,xs_code from sncn_zd_jg) d
 where a.clct_date>=to_date('20111016','yyyymmdd') and a.clct_date<=to_date('20111022','yyyymmdd')
  and b.zj_code=a.clct_bureau_org_code and d.xs_code=substr(a.rcv_area,1,4)
  and b.city in ( '合肥市','芜湖市' )and d.city in ('安庆市')
  group by a.clct_date,b.city,d.city order by a.clct_date,b.city,d.city;

--妥投总量:
select a.clct_date rq,b.city sjj,d.city jdj,count(*) ttzl from tb_evt_mail_clct a,tb_jg b,tb_evt_dlv c,tb_jg d
 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 b.zj_code=a.clct_bureau_org_code and d.zj_code=c.dlv_bureau_org_code
  and b.city in ( '合肥市','芜湖市' )and d.city in ('安庆市')
  group by a.clct_date,b.city,d.city order by a.clct_date,b.city,d.city;

--自投总量:
select a.clct_date rq,b.city sjj,d.city jdj,count(*) ztzl from tb_evt_mail_clct a,tb_jg b,tb_evt_dlv c,sncn_zd_jg d
 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 b.zj_code=a.clct_bureau_org_code and d.zj_code=c.dlv_bureau_org_code
  and b.city in ( '合肥市','芜湖市' )and d.city in ('安庆市')
  group by a.clct_date,b.city,d.city order by a.clct_date,b.city,d.city;

--自收自投总量:
select a.clct_date rq,b.city sjj,d.city jdj,count(*) zszt from tb_evt_mail_clct a,sncn_zd_jg b,tb_evt_dlv c,sncn_zd_jg d
 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 b.zj_code=a.clct_bureau_org_code and d.zj_code=c.dlv_bureau_org_code
  and b.city in ( '合肥市','芜湖市' )and d.city in ('安庆市')
  group by a.clct_date,b.city,d.city order by a.clct_date,b.city,d.city;

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

复制代码 代码示例:
select aa.rq,aa.sjj,aa.jdj,aa.sjzl,bb.zszl,cc.ttzl,dd.ztzl,ee.zszt from
(select a.clct_date rq,b.city sjj,d.city jdj,count(*) sjzl from tb_evt_mail_clct a,tb_jg b,(select distinct city,xs_code from sncn_zd_jg) d
 where a.clct_date>=to_date('20111016','yyyymmdd') and a.clct_date<=to_date('20111022','yyyymmdd')
  and b.zj_code=a.clct_bureau_org_code and d.xs_code=substr(a.rcv_area,1,4)
  and b.city in ( '合肥市','芜湖市' )and d.city in ('安庆市')
  group by a.clct_date,b.city,d.city order by a.clct_date,b.city,d.city) aa
left join
(select a.clct_date rq,b.city sjj,d.city jdj,count(*) zszl from tb_evt_mail_clct a,sncn_zd_jg b,(select distinct city,xs_code from sncn_zd_jg) d
 where a.clct_date>=to_date('20111016','yyyymmdd') and a.clct_date<=to_date('20111022','yyyymmdd')
  and b.zj_code=a.clct_bureau_org_code and d.xs_code=substr(a.rcv_area,1,4)
  and b.city in ( '合肥市','芜湖市' )and d.city in ('安庆市')
  group by a.clct_date,b.city,d.city order by a.clct_date,b.city,d.city) bb
  on aa.rq=bb.rq and aa.sjj=bb.sjj and aa.jdj=bb.jdj
left join
(select a.clct_date rq,b.city sjj,d.city jdj,count(*) ttzl from tb_evt_mail_clct a,tb_jg b,tb_evt_dlv c,tb_jg d
 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 b.zj_code=a.clct_bureau_org_code and d.zj_code=c.dlv_bureau_org_code
  and b.city in ( '合肥市','芜湖市' )and d.city in ('安庆市')
  group by a.clct_date,b.city,d.city order by a.clct_date,b.city,d.city) cc
  on aa.rq=cc.rq and aa.sjj=cc.sjj and aa.jdj=cc.jdj
left join
(select a.clct_date rq,b.city sjj,d.city jdj,count(*) ztzl from tb_evt_mail_clct a,tb_jg b,tb_evt_dlv c,sncn_zd_jg d
 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 b.zj_code=a.clct_bureau_org_code and d.zj_code=c.dlv_bureau_org_code
  and b.city in ( '合肥市','芜湖市' )and d.city in ('安庆市')
  group by a.clct_date,b.city,d.city order by a.clct_date,b.city,d.city) dd
  on aa.rq=dd.rq and aa.sjj=dd.sjj and aa.jdj=dd.jdj
left join
(select a.clct_date rq,b.city sjj,d.city jdj,count(*) zszt from tb_evt_mail_clct a,sncn_zd_jg b,tb_evt_dlv c,sncn_zd_jg d
 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 b.zj_code=a.clct_bureau_org_code and d.zj_code=c.dlv_bureau_org_code
  and b.city in ( '合肥市','芜湖市' )and d.city in ('安庆市')
  group by a.clct_date,b.city,d.city order by a.clct_date,b.city,d.city) ee
  on aa.rq=ee.rq and aa.sjj=ee.sjj and aa.jdj=ee.jdj;

注意:连接条件是所有查询中结果相同的字段,即aa.rq=ee.rq and aa.sjj=ee.sjj and aa.jdj=ee.jdj;
 
附:JOIN语句语法:

JOIN用于根据两个或多个表中的列之间的关系,从这些表中查询数据。

JOIN: 如果表中有至少一个匹配,则返回行
LEFT JOIN: 即使右表中没有匹配,也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配,也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配,就返回行

SQL INNER JOIN 关键字
在表中存在至少一个匹配时,INNER JOIN 关键字返回行。
INNER JOIN 关键字语法
 

复制代码 代码示例:
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
 

注释:INNER JOIN 与 JOIN 是相同的。

SQL LEFT JOIN 关键字
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。

LEFT JOIN 关键字语法
 

复制代码 代码示例:
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

SQL RIGHT JOIN 关键字
RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行,即使在左表 (table_name1) 中没有匹配的行。

RIGHT JOIN 关键字语法
 

复制代码 代码示例:
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

注释:在某些数据库中, RIGHT JOIN 称为 RIGHT OUTER JOIN。

SQL FULL JOIN 关键字
只要其中某个表存在匹配,FULL JOIN 关键字就会返回行。

FULL JOIN 关键字语法
 

复制代码 代码示例:
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
 

注释:在某些数据库中, FULL JOIN 称为 FULL OUTER JOIN。