oracle sql用于根据成绩计算gpa

Question

嗨，我是Oracle SQL的新手，我想写一个SQL语句，可以从表中读取成绩并为学生生成gpa。下面是我写的一个示例代码，它不能工作：

select id,
declare gpa = 0;
BEGIN
    IF grade = 'A+' THEN gpa = gpa + 4.5;
    ELSIF grade = 'A' THEN gpa = gpa + 4;
    ELSIF grade = 'B+' THEN gpa = gpa + 3.5;
    ELSIF grade = 'B' THEN gpa = gpa + 3;
    ELSIF grade = 'C+' THEN gpa = gpa + 2.5;
    ELSIF grade = 'C' THEN gpa = gpa + 2;
    ELSE gpa = gpa + 0;
    END IF
    gpa = gpa/count(grade)
END
gpa from table where id in ('s11','s12','s13','s14')
group by id

表中的数据示例如下：

id                   grade
--------------------------
s11                  A
s11                  B+
s11                  A+
s11                  C
s12                  C+
s12                  A
s12                  D
s12                  B
s13                  A+
s13                  C+
s13                  B
s13                  A
s13                  A
....
....
....
....

有人能帮帮忙吗？

Answer 1

尝试如下所示：

select id, sum(case name 
                    when 'A+' then 4.5 
                    when 'A' then 4
                    when 'B+' then 3.5
                    when 'B' then 3
                    when 'C+' then 2.5
                    when 'C' then 2
                    else 0
               end
               )/count(*) gpa
from table 
where id in ('s11','s12','s13','s14')
group by id