急问大神，关于分组为空的SQL,该如何处理_Oracle开发

急问大神，关于分组为空的SQL
员工成绩打分人
A 合格 g
B 合格 g
C 合格 g
D 不合格 g

根据员工分组找出g打分的不合格的只有D，总数为1，但是我需要的结果是

A 0
B 0
C 0
D 1

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

SQL code

with t as(select 'A' yg,'合格' cj,'g' dfr from dualunion allselect 'B','合格','g' from dualunion allselect 'C','合格','g' from dualunion allselect 'D','不合格','g' from dual)select yg,sum(decode(cj,'不合格',1,0)) c from t group by ygYG          C-- ----------A           0B           0C           0D           1
------解决方案--------------------
SQL codewith t as(select 'A' id,'合格' cj from dualunion allselect 'A','合格' from dualunion allselect 'B','不合格' from dualunion allselect 'A','不合格' from dual)select id,round(sum(decode(cj,'合格',1,0))/count(1),4)*100||'%' from t group by idID ROUND(SUM(DECODE(CJ,'合格',1,0-- -----------------------------------------A  66.67%B  0%
------解决方案--------------------
SQL codewith t as(select 'A' id,'合格' cj from dualunion allselect 'A','合格' from dualunion allselect 'B','不合格' from dualunion allselect '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                              67B                               0
------解决方案--------------------
探讨
可以教下我吗 用decode 如果70% 就获得100分 每下降0.2个百分点就扣2分 扣完就是0分 如果超过70% 就是100分 谢谢高手