博客

1、第一步先创造几条学生成绩数据

drop table chengji;
create table chengji (
  stu_id         varchar2(30) ,
  kemu       		varchar2(30),
  chengji        varchar2(50)
);
insert into chengji(stu_id, kemu, chengji) values ('student1','语文',123);
insert into chengji(stu_id, kemu, chengji) values ('student1','数学',125);
insert into chengji(stu_id, kemu, chengji) values ('student1','英语',128);
insert into chengji(stu_id, kemu, chengji) values ('student2','语文',110);
insert into chengji(stu_id, kemu, chengji) values ('student2','数学',120);
insert into chengji(stu_id, kemu, chengji) values ('student2','英语',130);
insert into chengji(stu_id, kemu, chengji) values ('student3','语文',129);
insert into chengji(stu_id, kemu, chengji) values ('student3','数学',126);
insert into chengji(stu_id, kemu, chengji) values ('student3','英语',125);
commit;

2、查看chengji表数据

select * from chengji;

3、行转列（用sum(case when)）

select stu_id
,sum(case when kemu='数学' then chengji else '0' end) as "数学"
,sum(case when kemu ='语文' then chengji  else '0' end) as "语文"
,sum(case when kemu ='英语' then chengji else '0' end) as "英语"
from chengji
group by stu_id;

结果如下：