oracle学生成绩行转列

不知名的尔今夏

于 2023-03-07 17:03:14 发布

阅读量281

点赞数

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/qq_40062060/article/details/129385952

版权

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;