现有两张表,成绩表和学籍表
成绩表:Score
学号 | 科目 | 成绩 |
---|---|---|
1 | 语文 | 65 |
1 | 数学 | 83 |
2 | 语文 | 75 |
2 | 数学 | 72 |
学籍表:info
学号 | 姓名 | 班级 |
---|---|---|
1 | 张三 | 一班 |
2 | 李四 | 二班 |
编写sql语句将两张表的数据查询成这样的结构
学号 | 姓名 | 语文 | 数学 | 总分 |
---|
方法一
首先在成绩表Score中进行如下查询SELECT `学号`,
SUM(CASE `科目` WHEN '语文' THEN `成绩` END) AS '语文',
SUM(CASE `科目` WHEN '数学' THEN `成绩` END) AS '数学'
FROM Score
GROUP BY `学号`
#注:此处使用CASE语句或DECODE语句均可;
得到结果:
学号 | 语文 | 数学 |
---|---|---|
1 | 65 | 83 |
2 | 75 | 72 |
将得到的结果作为新的数据表连接查询
SELECT a.`学号`,b.`姓名`,a.`语文`,a.`数学`,(a.`语文`+a.`数学`) AS `总分`
FROM
(SELECT `学号`,
SUM(CASE `科目` WHEN '语文' THEN `成绩` END) AS `语文`,
SUM(CASE `科目` WHEN '数学' THEN `成绩` END) AS `数学`
FROM Score
GROUP BY `学号`) a,info b
WHERE a.`学号` = b.`学号`
最终得到结果:
学号 | 姓名 | 语文 | 数学 | 总分 |
---|---|---|---|---|
1 | 张三 | 65 | 83 | 148 |
2 | 李四 | 75 | 72 | 147 |
方法二
使用PIVOT函数查询成绩表ScoreSELECT *
FROM [Score]
PIVOT (SUM(成绩) FOR 科目 IN ([语文],[数学])) AS T
同样得到临时表:
学号 | 语文 | 数学 |
---|---|---|
1 | 65 | 83 |
2 | 75 | 72 |
将临时表与学籍表info联查
select a.学号,a.姓名,b.语文,b.数学,(b.语文 + b.数学) as 总分
from info a
join (SELECT * FROM [Score]
PIVOT (SUM(成绩) FOR 科目 IN ([语文],[数学])) AS T) b on a.学号 = b.学号
得到最终的结果:
学号 | 姓名 | 语文 | 数学 | 总分 |
---|---|---|---|---|
1 | 张三 | 65 | 83 | 148 |
2 | 李四 | 75 | 72 | 147 |