如何将多個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。