【数据库】oracle 多表拼接，Left join将SELECT出的所有结果拼接成一张表。

  项目中遇到这样的问题，需要按指定的时间区间从10个数据库表中查询到关于10个司法局的业务数量，案件数量等11列信息。再对这些结果排序。 

  那么我们怎么通过一个SQL语句来解决呢？

  下面介绍下SQL方法：

select 
    a.*,
    a.id,
    b.id, 
c.price 
from  author a 
left join  book b on a.id=b.id 
left join  order c on a.id=c.id

    使用Left join -on语句将3张表链接到了一起。对于项目的解决办法，可以将book，order，auther替换成sql语句，即select出来的表，再做一次left join

    SQL语句如下

select 
    a.MEDIATIONCOMMITTEE,
    a.tiaojieaj,
    b.shangbaosl,
    c.paichafk,
    d.zixunrz,
    e.chunjufw,
    f.faxuanhd
    from (select MEDIATIONCOMMITTEE,count(1) as tiaojieaj from MBM_CASE where DATEACCEPTED <= to_date('2018-05-10 00:00:00','yyyy-mm-dd hh24:mi:ss') group by MEDIATIONCOMMITTEE) a
    left join (select MEDIATIONCOMMITTEE,count(1) as shangbaosl from MCS_MEDIATIONCASE where REPORTDATE between to_date('2016-01-10 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2018-05-10 00:00:00','yyyy-mm-dd hh24:mi:ss') and STATUS !='ToBeReported' group by MEDIATIONCOMMITTEE) b 
    on a.MEDIATIONCOMMITTEE=b.MEDIATIONCOMMITTEE
    left join (select MEDIATIONCOMMITTEE,count(1) as paichafk from CDS_INVESTIGATIONFEEDBAC where DATE_ between to_date('2016-01-10 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2018-05-10 00:00:00','yyyy-mm-dd hh24:mi:ss') group by MEDIATIONCOMMITTEE) c
    on a.MEDIATIONCOMMITTEE=c.MEDIATIONCOMMITTEE
    left join (select MEDIATIONCOMMITTEE,count(1) as zixunrz from AMS_VILLAGESERVICELOG where CREATEDATE between to_date('2016-01-10 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2018-05-10 00:00:00','yyyy-mm-dd hh24:mi:ss') group by MEDIATIONCOMMITTEE    ) d
    on a.MEDIATIONCOMMITTEE=d.MEDIATIONCOMMITTEE
    left join (select MEDIATIONCOMMITTEE,count(1) as chunjufw from WWS_CONSULT where DATE_ between to_date('2016-01-10 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2018-05-10 00:00:00','yyyy-mm-dd hh24:mi:ss') group by MEDIATIONCOMMITTEE  ) e
    on a.MEDIATIONCOMMITTEE=e.MEDIATIONCOMMITTEE
    left join (select MEDIATIONCOMMITTEE,count(1) as faxuanhd from WWS_LEGALACTIVITY   where STARTDATE between to_date('2016-01-10 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2018-05-10 00:00:00','yyyy-mm-dd hh24:mi:ss') group by MEDIATIONCOMMITTEE) f
    on a.MEDIATIONCOMMITTEE=f.MEDIATIONCOMMITTEE