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