select x.datatime,
x.organame,
x.name1,
x.name2,
x.name3,
x.name4,
x.name5,
x.other,
z.num1,
z.numlv
from (select to_char(trunc(sysdate - 1, 'dd'), 'yyyy-mm-dd') datatime,
y.organame,
count(t.serialno),
SUM(DECODE(t.srtypeid, '004027001', 1, 0)) name1,
SUM(DECODE(t.srtypeid, '004027002', 1, 0)) name2,
SUM(DECODE(t.srtypeid, '004027003', 1, 0)) name3,
SUM(DECODE(t.srtypeid, '004027004', 1, 0)) name4,
SUM(DECODE(t.srtypeid, '004027005', 1, 0)) name5,
SUM(DECODE(t.srtypeid, '004027006', 1, 0)) other
from problemprocess t
right join cityinfo y
on t.acceptcity = y.orgaid
and t.acceptstaffno like '880%'
and t.srtypeid like '004027%'
and t.serialno >= to_char(trunc(sysdate - 1, 'dd'), 'yyyymmdd')
and t.serialno < to_char(trunc(sysdate, 'dd'), 'yyyymmdd')
where y.orgaid not like '186%'
group by y.organame) x,
(select a.organame,
num1,
case
when num1 = 0 then
0
else
round(num2 / num1, 4) * 100
end numlv
from (select count(t.serialno) num1, y.organame
from problemprocess t
right join cityinfo y
on t.acceptcity = y.orgaid
and t.acceptstaffno like '880%'
and t.srtypeid like '004027%'
and t.serialno >=
to_char(trunc(sysdate, 'mm'), 'yyyymmdd')
and t.serialno < to_char(trunc(sysdate, 'dd'), 'yyyymmdd')
where y.orgaid not like '186%'
group by y.organame) a,
(select count(t.serialno) num2, y.organame
from problemprocess t
right join cityinfo y
on t.acceptcity = y.orgaid
and t.processstate = '30010004'
and t.acceptstaffno like '880%'
and t.srtypeid like '004027%'
and t.serialno >=
to_char(trunc(sysdate, 'mm'), 'yyyymmdd')
and t.serialno < to_char(trunc(sysdate, 'dd'), 'yyyymmdd')
where y.orgaid not like '186%'
group by y.organame) b
where a.organame = b.organame) z
where x.organame = z.organame


这你妈是一条sql ****