create table tab_test(cla varchar2(20),kecheng varchar(20),score number(12,2)) ; SELECT cla, kecheng, to_char(SUM(decode(sign(score - 60), -1, 0, 1)) / (SELECT COUNT(1) FROM tab_test b WHERE a.cla = b.cla) * 100)||'%' FROM tab_test a GROUP BY cla, kecheng ORDER BY cla,kecheng;
select class,kecheng,
Round(sum(case when chengji>=60 then 1 else 0 end)/count(*),2) as passrate
from student group by class,kecheng
---------- ---------- ---------- ----------
1 1 语文 80
1 2 语文 90
2 1 数学 90
2 2 语文 100
1 4 数学 87SQL> select class,subject,sum(score) score,count(*) num, sum(score)/count(*) pingjun from zzw_test342
2 group by class,subject; CLASS SUBJECT SCORE NUM PINGJUN
---------- ---------- ---------- ---------- ----------
1 数学 87 1 87
1 语文 170 2 85
2 数学 90 1 90
2 语文 100 1 100SQL>
kecheng,
to_char(SUM(decode(sign(score - 60), -1, 0, 1)) /
(SELECT COUNT(1) FROM tab_test b WHERE a.cla = b.cla) * 100)||'%'
FROM tab_test a
GROUP BY cla, kecheng
ORDER BY cla,kecheng;