sql语句总结
?
?
思路：
?
1：先需要想到行转列函数 decode 或 case
?
?select a.name 姓名,
??????? (case
????????? when course = '语文' then
?????????? score
??????? end) 语文,
??????? (case
????????? when course = '英语' then
?????????? score
??????? end) 英语,
??????? (case
????????? when course = '数学' then
?????????? score
??????? end) 数学,
??????? score
?? from test a
? order by a. name desc;
?
2：想到求列的总和（想到sum语句）
select a.name 姓名,
?????? sum(decode(course, '语文', score)) 语文,
?????? sum(decode(course, '数学', score)) 数学,
?????? sum(decode(course, '英语', score)) 英语,
?????? sum(score) 总分
? from test a
?group by a.name
?order by a. name desc;
?
另外一种方法(不推荐)
select a.name 姓名,
?????? sum(case
???????????? when course = '语文' then
????????????? score
?????????? end) 语文,
?????? sum(case
???????????? when course = '英语' then
????????????? score
?????????? end) 英语,
?????? sum(case
???????????? when course = '数学' then
????????????? score
?????????? end) 数学,
?????? b.total 总分
? from test a, (select name, sum(score) total from test group by name) b
?where a.name = b.name
?group by a.name, b.total
?order by a. name desc;
?
?
