mysql查询每个学生的各科成绩，以及总分和平均分（实操版）

目录

一、背景

二、题目简述

三、SQL实操（从建表到计算）

1、行变列（题目解答）

2、列变行（题目拓展）

四、参考文献

一、背景

最近要对数据进行分类、汇总，进行求和、求平均值等操作，故先拿一个简单的需求来练手。网上搜索了一个经典笔试题“mysql查询每个学生的各科成绩，以及总分和平均分”，但是实操发现，文献有各种错误，而且抄袭严重，连数据都不带改的，真是无语。功夫不负有心人，终于找个一个合适的文献，特做练习，记录如下。

二、题目简述

学生成绩表如下：

要求：查询学生每一门成绩及总分、平均分，效果如下：

三、SQL实操（从建表到计算）

1、行变列（题目解答）

-- 新建学生成绩表DROP TABLE IF EXISTS tb_score;create table tb_score(id int(11) not null auto_increment,user_no VARCHAR(32) not null comment '学生工号',subject varchar(32) comment '课程',score int(8) COMMENT '成绩',primary key(id)) ENGINE = INNODB DEFAULT CHARSET = utf8;-- 检查表SELECT * from tb_score;-- 插入测试数据INSERT INTO tb_score(user_no,subject,score) VALUES('001','语文',31);INSERT INTO tb_score(user_no,subject,score) VALUES('001','数学',32);INSERT INTO tb_score(user_no,subject,score) VALUES('001','英语',33);INSERT INTO tb_score(user_no,subject,score) VALUES('002','语文',41);INSERT INTO tb_score(user_no,subject,score) VALUES('002','数学',42);INSERT INTO tb_score(user_no,subject,score) VALUES('002','英语',43);INSERT INTO tb_score(user_no,subject,score) VALUES('003','语文',51);INSERT INTO tb_score(user_no,subject,score) VALUES('003','数学',52);INSERT INTO tb_score(user_no,subject,score) VALUES('003','英语',53);INSERT INTO tb_score(user_no,subject,score) VALUES('004','语文',61);INSERT INTO tb_score(user_no,subject,score) VALUES('004','数学',62);INSERT INTO tb_score(user_no,subject,score) VALUES('004','英语',63);INSERT INTO tb_score(user_no,subject,score) VALUES('004','政治',64);-- 进行计算（使用case...when....then 进行行转列）SELECT tb.user_no as '学号',SUM(CASE subject when '语文' then score else 0 end) as '语文',SUM(CASE subject when '数学' then score else 0 end) as '数学',SUM(CASE subject when '英语' then score else 0 end) as '英语',SUM(CASE subject when '政治' then score else 0 end) as '政治',sum(score) as '总分',avg(score) as '平均分'from tb_score tb GROUP BY tb.user_no;-- 进行计算（使用IF() 进行行转列：）SELECT tb.user_no as '学号',SUM(if (`subject` = '语文',score,0)) as '语文',SUM(if (`subject` = '数学',score,0)) as '数学',SUM(if (`subject` = '英语',score,0)) as '英语',SUM(if (`subject` = '政治',score,0)) as '政治',sum(score) as '总分',avg(score) as '平均分'from tb_score tb GROUP BY tb.user_no;-- 合并字段显示：利用group_concat()select tb.user_no,GROUP_CONCAT(tb.`subject`,':',tb.score) from tb_score tb GROUP BY tb.user_no;

2、列变行（题目拓展）

-- 新建另外一张表tb_score1DROP TABLE if EXISTS tb_score1;create table tb_score1(id int(11) not null auto_increment,user_no VARCHAR(32) COMMENT '学生工号',yuwen int(11) COMMENT '语文成绩',shuxue int(11) COMMENT '数学成绩',yingyu int(11) COMMENT '英语成绩',zhengzhi int(11) COMMENT '政治成绩',PRIMARY KEY(id)) ENGINE = INNODB DEFAULT CHARSET = utf8;-- 检查表SELECT * from tb_score1;-- 插入数据INSERT INTO tb_score1(user_no,yuwen,shuxue,yingyu,zhengzhi) VALUES('001',31,32,33,0);INSERT INTO tb_score1(user_no,yuwen,shuxue,yingyu,zhengzhi) VALUES('002',41,42,43,0);INSERT INTO tb_score1(user_no,yuwen,shuxue,yingyu,zhengzhi) VALUES('003',51,52,53,0);INSERT INTO tb_score1(user_no,yuwen,shuxue,yingyu,zhengzhi) VALUES('004',61,62,63,64);-- 计算/汇总SELECT user_no as '学号','语文' as '课程',yuwen as '成绩' from tb_score1union ALLSELECT user_no as '学号','数学' as '课程',shuxue as '成绩' from tb_score1union ALLSELECT user_no as '学号','英语' as '课程',yingyu as '成绩' from tb_score1union ALLSELECT user_no as '学号','政治' as '课程',zhengzhi as '成绩' from tb_score1ORDER BY '学号';

注意：

1、“列变行”进行group by的时候要根据别名，和“行变列”不一样

2、关于成绩平均数如何进行位数截取请参考上一篇文章

链接：SQL/Java计算公式汇总_无痕之剑的书橱-CSDN博客

四、参考文献

1、mysql 行转列 列转行

链接：https://www.cnblogs.com/xiaoxi/p/7151433.html

2、以下是跑不通的文献（而且搜索排名靠前）：

mysql查询每个学生的各科成绩，以及总分和平均分 – 海牙2018 – 博客园 等等