|
目录
一、背景
二、题目简述
三、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 - 博客园 等等
|