1.数据库
-
t_student表结构及数据
t_student -
t_score表结构及数据
t_score
2.需求
要求按照学生展示该生的各科成绩、总成绩、平均分，并按照总分进行排序，效果如下：
结果
3.mysql实现
- sql
SELECT
A.id '学号',
A.NAME '姓名',
MAX( CASE B.SUBJECT WHEN '数学' THEN B.score ELSE 0 END ) '数学',
MAX( CASE B.SUBJECT WHEN '语文' THEN B.score ELSE 0 END ) '语文',
MAX( CASE B.SUBJECT WHEN '英语' THEN B.score ELSE 0 END ) '英语',
SUM( B.score ) '总分',
AVG( B.score ) '平均分'
FROM
t_student A
LEFT JOIN t_score B ON A.id = B.s_id
GROUP BY
A.id
ORDER BY
SUM( B.score ) DESC
或者
SELECT
A.id '学号',
A.NAME '姓名',
MAX( CASE WHEN B.SUBJECT = '数学' THEN B.score ELSE 0 END ) '数学',
MAX( CASE WHEN B.SUBJECT = '语文' THEN B.score ELSE 0 END ) '语文',
MAX( CASE WHEN B.SUBJECT = '英语' THEN B.score ELSE 0 END ) '英语',
SUM( B.score ) '总分',
AVG( B.score ) '平均分'
FROM
t_student A
LEFT JOIN t_score B ON A.id = B.s_id
GROUP BY
A.id
ORDER BY
SUM( B.score ) DESC
- 函数说明
MAX：取最大
SUM：求和
AVG：求平均
CASE：两种用法参考 http://c.biancheng.net/mysql/case.html
4.oracle实现
- sql
SELECT
A.id '学号',
A.NAME '姓名',
MAX( DECODE( B.SUBJECT, '数学', B.score, 0 ) ) '数学',
MAX( DECODE( B.SUBJECT, '语文', B.score, 0 ) ) '语文',
MAX( DECODE( B.SUBJECT, '英语', B.score, 0 ) ) '英语',
SUM( B.score ) '总分',
AVG( B.score ) '平均分'
FROM
t_student A
LEFT JOIN t_score B ON A.id = B.s_id
GROUP BY
A.id
ORDER BY
SUM( B.score ) DESC
- 函数
MAX：取最大
SUM：求和
AVG：求平均
DECODE：DECODE(value, if1, then1, if2, then2, ..., ifN, thenN, else)
DECODE(条件, 值1, 返回值1, 值2, 返回值2, ..., 值N, 返回值N, 缺省值)
DECODE函数与mysql的CASE函数大同小异，只是语法稍稍有所差别。
