学生分数汇总查询

select * from t_score;
create table t_score
(
  id number,
  name varchar2(32),
  courseid varchar2(32),
  score number
);


insert into t_score values(1,'zhangsan','math',80);
insert into t_score values(1,'zhangsan','chinese',90);
insert into t_score values(1,'zhangsan','english',70);


insert into t_score values(2,'lisi','math',50);
insert into t_score values(2,'lisi','chinese',80);
insert into t_score values(2,'lisi','english',60);


select t.name,
       sum(case
             when t.courseid = 'math' then
              t.score
           end) as "数学",
       sum(case
             when t.courseid = 'chinese' then
              t.score
           end) as "语文",
       sum(case
             when t.courseid = 'english' then
              t.score
           end) as "英语",
       sum(t.score) as "总分"
  from t_score t
 group by t.id, t.name order by "总分" desc;