1.有score列表如下:(列转行)
第一步,建表
create table score(
name varchar2(15) ,
course varchar(15) not null,
scrore number(5) not null
);
插入数据:
insert into score values(‘张三’,‘语文’,80);
insert into score values(‘张三’,‘数学’,90);
insert into score values(‘张三’,‘英语’,85);
insert into score values(‘李四’,‘语文’,70);
insert into score values(‘李四’,‘数学’,80);
insert into score values(‘李四’,‘英语’,89);
commit;
效果图:
第二步,转换为行
–将列转为行
select name,
max(case course when ‘语文’ then scrore end) as “语文”,
max(case course when ‘数学’ then scrore end) as “数学”,
max(case course when ‘英语’ then scrore end) as “英语”
from score
group by name;
sum min 都可以
或者:
select name,
max(case when (course=‘语文’) then scrore end) as “语文”,
max(case when (course=‘数学’) then scrore end) as “数学”,
max(case when (course=‘英语’) then scrore end) as “英语”
from score
group by name;
从score中挑选数据,按姓名分组,用 case ,when 选择相符合的科目的分数输出。
结果: