目录

一、背景

二、题目简述

三、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_score1
DROP 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_score1
union ALL
SELECT user_no as '学号','数学' as '课程',shuxue as '成绩' from tb_score1
union ALL
SELECT user_no as '学号','英语' as '课程',yingyu as '成绩' from tb_score1
union ALL
SELECT user_no as '学号','政治' as '课程',zhengzhi as '成绩' from tb_score1
ORDER BY '学号';

注意：

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

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

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

四、参考文献

1、mysql 行转列 列转行

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

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

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