mysql统计每个科目平均成绩_mysql、oracle查询学生的各科成绩、总成绩、平均分...

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函数大同小异，只是语法稍稍有所差别。