项目中遇到这样的问题，需要按指定的时间区间从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