--try t为你的表名 select rgn, to_char(times, 'yyyy') year, to_char(times, 'mm') month, count(decode(ispass,1,1,null))/count(1) pass from t group by rgn, to_char(times, 'yyyy'), to_char(times, 'mm') order by rgn, to_char(times, 'yyyy'), to_char(times, 'mm');
木调试过,可以参考下.. SELECT rgn, To_Char(times,'yyyy') year, To_Char(times,'mm') month, Sum(Decode(ispass,1,1,0))/Count(*) over (PARTITION BY To_Char(times,'yyyy'),To_Char(times,'mm')) pass FROM t ORDER BY times;
本人拼接出来的,别拍砖。。 select d.rgn,d.year,d.month,c.pass from (select a.rgn,a.year,b.month from ( select rgn, count(*) year from recheck where times >=trunc(sysdate,'YYYY') and times<=add_months(trunc(sysdate,'YYYY'),12)-1 group by rgn ) a join ( select rgn, count(*) month from recheck where times >=TRUNC(SYSDATE, 'MM') and times<=last_day(SYSDATE) group by rgn )b on a.rgn=b.rgn ) d join (select rgn,cast((sum(case when ispass<>1 then 1 else 0 end)*100/count(*)) as number(10,2)) pass from recheck group by rgn ) c on d.rgn=c.rgn
--try t为你的表名
select
rgn,
to_char(times, 'yyyy') year,
to_char(times, 'mm') month,
count(decode(ispass,1,1,null))/count(1) pass
from t
group by rgn, to_char(times, 'yyyy'), to_char(times, 'mm')
order by rgn, to_char(times, 'yyyy'), to_char(times, 'mm');
SELECT rgn,
To_Char(times,'yyyy') year,
To_Char(times,'mm') month,
Sum(Decode(ispass,1,1,0))/Count(*) over (PARTITION BY To_Char(times,'yyyy'),To_Char(times,'mm')) pass
FROM t ORDER BY times;
select d.rgn,d.year,d.month,c.pass from
(select a.rgn,a.year,b.month from
(
select rgn, count(*) year from recheck where times >=trunc(sysdate,'YYYY') and times<=add_months(trunc(sysdate,'YYYY'),12)-1 group by rgn
) a join (
select rgn, count(*) month from recheck where times >=TRUNC(SYSDATE, 'MM') and times<=last_day(SYSDATE) group by rgn
)b
on a.rgn=b.rgn ) d join
(select rgn,cast((sum(case when ispass<>1 then 1 else 0 end)*100/count(*)) as number(10,2)) pass from recheck group by rgn ) c
on d.rgn=c.rgn
不必这般麻烦