with t as(
select 'A' yg,'合格' cj,'g' dfr from dual
union all
select 'B','合格','g' from dual
union all
select 'C','合格','g' from dual
union all
select 'D','不合格','g' from dual
)select yg,sum(decode(cj,'不合格',1,0)) c from t group by yg
YG          C
-- ----------
A           0
B           0
C           0
D           1

------解决方案--------------------

SQL code

with t as(
select 'A' id,'合格' cj from dual
union all
select 'A','合格' from dual
union all
select 'B','不合格' from dual
union all
select 'A','不合格' from dual
)select id,round(sum(decode(cj,'合格',1,0))/count(1),4)*100||'%' from t group by id
ID ROUND(SUM(DECODE(CJ,'合格',1,0
-- -----------------------------------------
A  66.67%
B  0%

------解决方案--------------------

SQL code
with t as(
select 'A' id,'合格' cj from dual
union all
select 'A','合格' from dual
union all
select 'B','不合格' from dual
union all
select 'A','不合格' from dual
)
select id,
       case
         when sum(decode(cj, '合格', 1, 0)) / count(1) >= 0.7 then
          100
         when sum(decode(cj, '合格', 1, 0)) / count(1) < 0.7 then
          GREATEST(100 - round((0.7 - sum(decode(cj, '合格', 1, 0)) / count(1)) /
                               0.001),
                   0)
       end
  from t
 group by id
 
ID   CASEWHENSUM(DECODE(CJ,'合格',1
-- ------------------------------
A                              67
B                               0

------解决方案--------------------

探讨

可以教下我吗 用decode 如果70% 就获得100分 每下降0.2个百分点就扣2分 扣完就是0分 如果超过70% 就是100分 谢谢高手