优化前SQL:
select rownum r,
a.*,
(select count(0) ymbjgrs
from (select c.xh, b.bjdm, count(*) bjgks
from cjb c
left join view_xsjbxx b
on c.xh = b.xh
where c.xn = '2016-2017'
and c.xq = '01'
and c.cj <
(select dycj from cjdzb where dydj like '%不及格%')
group by c.xh, b.bjdm)
where bjgks =1 or bjgks = 1
and bjdm = a.bjdm) ymbjgrs,
(select count(0) lmbjgrs
from (select c.xh, b.bjdm, count(*) bjgks
from cjb c
left join view_xsjbxx b
on c.xh = b.xh
where c.xn = '2016-2017'
and c.xq = '01'
and c.cj <
(select dycj from cjdzb where dydj like '%不及格%')
group by c.xh, b.bjdm)
where bjgks =1 or bjgks = 2
and bjdm = a.bjdm) lmbjgrs,
(select count(0) smbjgrs
from (select c.xh, b.bjdm, count(*) bjgks
from cjb c
left join view_xsjbxx b
on c.xh = b.xh
where c.xn = '2016-2017'
and c.xq = '01'
and c.cj <
(select dycj from cjdzb where dydj like '%不及格%')
group by c.xh, b.bjdm)
where bjgks =1 or bjgks = 3
and bjdm = a.bjdm) smbjgrs,
(select count(0) simbjgrs
from (select c.xh, b.bjdm, count(*) bjgks
from cjb c
left join view_xsjbxx b
on c.xh = b.xh
where c.xn = '2016-2017'
and c.xq = '01'
and c.cj <
(select dycj from cjdzb where dydj like '%不及格%')
group by c.xh, b.bjdm)
where bjgks =1 or bjgks = 4
and bjdm = a.bjdm) simbjgrs,
(select count(0) wmbjgrs
from (select c.xh, b.bjdm, count(*) bjgks
from cjb c
left join view_xsjbxx b
on c.xh = b.xh
where c.xn = '2016-2017'
and c.xq = '01'
and c.cj <
(select dycj from cjdzb where dydj like '%不及格%')
group by c.xh, b.bjdm)
where bjgks =1 or bjgks = 5
and bjdm = a.bjdm) wmbjgrs,
(select count(0) bjrs fr