编写一条sql语句将表1(stuScore)中的纵向排列数据改写成表2
表1(stuScore):
id | stuName | courseName | score |
1 | 大李 | 语文 | 99.5 |
2 | 大李 | 数学 | 79.5 |
3 | 大李 | 英语 | 49.5 |
4 | 小李 | 语文 | 89.5 |
5 | 小李 | 数学 | 69.5 |
6 | 小李 | 英语 | 59.5 |
表2:
id | stuName | chineseScore | mathScore | englishScore |
1 | 大李 | 99.5 | 79.5 | 49.5 |
2 | 小王 | 89.5 | 69.5 | 59.5 |
stuScore建表语句:
--创建表格
create table stuScore(
id number(6),
stuName varchar2(50),
courseName varchar2(50),
score number(6,1),
primary key(id)
);
--插入数据
insert into stuScore (Id, Stuname, Coursename, Score)
values (1, '大李', '语文', 99.5);
insert into stuScore (Id, Stuname, Coursename, Score)
values (2, '大李', '数学', 79.5);
insert into stuScore (Id, Stuname, Coursename, Score)
values (3, '大李', '英语', 49.5);
insert into stuScore (Id, Stuname, Coursename, Score)
values (4, '小王', '语文', 89.5);
insert into stuScore (Id, Stuname, Coursename, Score)
values (5, '小王', '数学', 69.5);
insert into stuScore (Id, Stuname, Coursename, Score)
values (6, '小王', '英语', 59.5);
commit;
查询语句:
写法1:
select stuName,
sum(decode(courseName,'语文',score,null)) as chineseScore,
sum(decode(courseName,'数学',score,null)) as mathScore,
sum(decode(courseName,'英语',score,null)) as englishScore
from stuScore group by stuName;
写法2:
select stuName,
sum(case courseName when '语文' then score end) as chineseScore,
sum(case courseName when '数学' then score end) as mathScore,
sum(case courseName when '英语' then score end) as englishScore
from stuScore group by stuName;