mysql查询每个学生的各科成绩,以及总分和平均分(实操版)

本文详细介绍了如何使用SQL进行行变列和列变行操作,以解决查询每个学生各科成绩、总分和平均分的问题,并提供了正确建表、插入数据和计算示例。通过案例展示了CASE语句和IF函数在行转列中的应用,以及GROUP_CONCAT函数用于合并字段显示。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

目录

一、背景

二、题目简述

三、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 - 博客园 等等

<think>好的,我现在需要帮助用户解决在MySQL中计算总分的平均值的问题。首先,我需要仔细理解用户的需求。用户提到“计算总分的平均值”,这里的总分应该是指每个学生各科成绩,然后求所有学生总分的平均值。 接下来,我需要回顾一下相关的SQL知识,尤其是聚合函数的使用。总分通常是通过SUM函数对各科成绩相加得到的,而平均值则是用AVG函数。但是这里的情况是,先计算每个学生总分,再对这些总分求平均值。所以可能需要两个步骤:首先为每个学生计算总分,然后对这些总分取平均。 这时候,我想到可能需要使用子查询或者分组后再聚合。例如,先通过GROUP BY按学生分组,计算每个学生总分,然后将这个结果作为子查询,再使用AVG函数求平均。或者,如果每个学生只有一条记录,可以直接在一条SELECT语句中使用SUMAVG的组合。 但需要考虑表的结构。根据用户提供的引用,比如引用[1]中的t_score表,可能每个学生各科成绩存储在同一行,这样每个学生总分可以通过SUM(chinese + math + english + physics + chemistry)得到,然后直接使用AVG函数对这个总分求平均值。这种情况下,不需要分组,因为每个学生只有一条记录,总分是单行的值。但如果是每个学生有多条记录,比如不同科目分布在不同的行,那么可能需要先按学生分组计算总分,再求平均。 不过,根据用户的问题描述,他们提到“总分的平均值”,也就是先计算每个学生总分,再求这些总分的平均值。这时候正确的做法应该是先为每个学生生成总分,然后对这些总分取平均。例如,使用子查询先得到每个学生总分,再在外部查询中使用AVG。 例如: SELECT AVG(total_score) AS average_total FROM ( SELECT name, SUM(chinese + math + english + physics + chemistry) AS total_score FROM t_score GROUP BY name ) AS subquery; 这样,子查询先按学生分组计算总分,外部查询再计算这些总分的平均值。 但是,如果每个学生在表中只有一条记录,且各科成绩都在同一行,那么可能不需要GROUP BY,直接对每个学生总分求平均: SELECT AVG(chinese + math + english + physics + chemistry) AS average_total FROM t_score; 不过,这两种情况的区别在于,如果有多个记录属于同一个学生,是否需要合并。例如,如果表中有多个记录对应同一个学生每个记录有不同的科目成绩,这时候需要用GROUP BY来合并计算总分,再求平均。但根据引用中的例子,如引用[1],t_score表中每个学生可能每个科目有一条记录,或者各科成绩在同一行? 需要进一步分析用户提供的引用内容。引用[1]中的SQL语句是GROUP BY name,计算每个学生总分平均分,说明表中可能存在同一个学生多个科目的记录,或者每个学生各科成绩在同一行。例如,引用[1]中的SELECT语句使用了SUM(chinese + math + ...),这可能意味着每个学生各科成绩在同一行,因此每个学生对应一条记录,包含所有科目成绩。这样,计算总分时只需要SUM各科成绩,按name分组,但每个name可能只有一条记录?这可能不太合理,因为GROUP BY name通常用于同一学生有多条记录的情况,比如每个学生的不同科目成绩分布在多行。例如,可能表结构是每个学生每个科目有一条记录,这时候需要按name分组,对每个学生各科成绩。 例如,如果表结构是每个学生每个科目分数单独一行,比如: name | subject | score 张三 | 语文 | 89 张三 | 数学 | 80 ... 那么计算每个学生总分需要按name分组,SUM(score)。然后计算这些总分的平均值,就需要先得到每个学生总分,再求平均。 但根据用户提供的引用[1]中的SQL语句,是SUM(chinese + math + english + physics + chemistry),这似乎意味着每个学生各科成绩都存储在同一个记录的列中。例如,表中的每条记录包含name、chinese、math等列,每个学生只有一条记录,包含所有科目成绩。这种情况下,计算每个学生总分就是SUM这些列的,但此时每个学生只有一条记录,所以GROUP BY name可能没有实际效果,因为每个name只有一行。这种情况下,计算所有学生总分平均值可以直接用AVG(chinese + math + ...),而无需分组。 但用户提供的引用[1]中的例子中使用了GROUP BY name,这可能意味着表中可能存在多个同名的学生?或者可能表中的结构不同,比如每个学生各科成绩分布在多行中,每行代表一个科目?这需要更仔细地分析。 例如,引用[2]中的例子创建了一个stud表,结构是id, name, chinese, math, english,每个学生一条记录,各科成绩作为列。这时,计算每个学生总分是SUM(chinese + math + english),然后求平均的话,直接AVG(chinese + math + english)即可,因为每个学生只有一条记录,所有学科成绩在同一行。 但在这种情况下,如果用户的问题是计算所有学生总分的平均值,即每个学生总分相加后的平均值,那么正确的SQL应该是: SELECT AVG(chinese + math + english) AS average_total FROM stud; 但如果用户想先计算每个学生总分,然后对这些总分取平均值,那么当每个学生只有一条记录时,两种方法的结果是一样的。例如,如果有三个学生总分分别是200, 250, 300,那么平均是250。而AVG(chinese + math + english)也会计算这三个值的平均。 但如果表中每个学生有多条记录,比如同一学生有多个学期的成绩,那么可能需要先按学生分组计算每个学生总分,然后求平均。例如: SELECT AVG(total_score) FROM ( SELECT name, SUM(chinese + math + english) AS total_score FROM stud GROUP BY name ) AS subquery; 因此,用户的问题需要明确表结构。但根据用户提供的引用,特别是引用[1]中的SQL,可能表中的结构是每个学生各科成绩在同一行,所以GROUP BY name可能是不必要的,除非存在同名学生。但通常name不是唯一标识,所以可能需要用id分组。不过,在引用[1]的例子中,GROUP BY name被用来计算每个学生总分,这可能意味着表中每个学生的记录可能有多条,比如每个学生的不同科目成绩分布在不同的行中,但各科作为列存储,这可能不太合理。因此,可能存在表结构的不同理解。 可能用户的问题中的表结构是每个学生各科成绩在同一行,比如像引用[2]中的stud表,每个学生各科成绩作为列存在同一行。因此,每个学生总分各科,而总分的平均值就是所有学生总分的平均值。此时,正确的SQL应该是: SELECT AVG(chinese + math + english + physics + chemistry) AS average_total FROM t_score; 但用户可能需要的是每个学生总分,然后对这些总分取平均。例如,假设有学生A的总分是500,学生B的总分是600,平均是550。使用上面的SQL会直接计算所有学生各科的平均值,等同于每个学生总分的平均值。 所以在这种情况下,正确的答案可能不需要子查询,直接使用AVG函数对各科求平均即可。 但用户的问题可能更复杂,例如,每个学生的不同科目成绩分布在多行中,这时候需要先按学生分组计算总分,再求平均。例如,如果表结构是: name | subject | score 张三 | 语文 | 90 张三 | 数学 | 85 李四 | 语文 | 80 ... 那么计算每个学生总分需要先按name分组,SUM(score),然后求这些总分的平均值。此时,SQL应该是: SELECT AVG(total_score) AS average_total FROM ( SELECT name, SUM(score) AS total_score FROM t_score GROUP BY name ) AS subquery; 但根据用户提供的引用,可能表结构是各科成绩作为列,而不是行。例如,引用[1]中的SUM(chinese + math + ...),这说明各科是表中的列。因此,每个学生对应一行,包含各科成绩。所以在这种情况下,计算总分的平均值只需要对各科求平均即可,不需要分组。例如: SELECT AVG(chinese + math + english + physics + chemistry) AS average_total_score FROM t_score; 但用户的问题是“计算总分的平均值”,即先计算每个学生总分,然后对这些总分求平均。在这种情况下,如果每个学生只有一行记录,那么用上述方法即可,因为每个学生总分各科,AVG函数会计算所有行的这个总的平均值。 比如,表中有三个学生,他们的各科分别是200, 250, 300,那么AVG的结果就是250。 因此,正确的SQL语句可能是: SELECT AVG(chinese + math + english + physics + chemistry) AS average_total_score FROM t_score; 但用户可能需要更明确的步骤,例如,先计算每个学生总分,然后求平均。这时候可能需要子查询,但根据表结构的不同,可能子查询是否有必要。 例如,在引用[1]的例子中,用户给出的SQL是: SELECT name, SUM(chinese + math + english + physics + chemistry) AS total_score, AVG(chinese + math + english + physics + chemistry) AS average_score FROM t_score GROUP BY name; 这说明该表中的记录可能按学生分组,可能每个学生有多条记录,各科成绩在每条记录中,但每个学生有多个记录,每个记录可能包含不同科目成绩?或者可能每个学生有多个科目成绩分布在不同的行? 但如果是这样的话,SUM(chinese + math + ...)可能有问题,因为每个记录的chinese列可能有值,而其他科目列为0或其他值。这可能不太合理。另一种可能是表中的每个记录代表一个学生的所有科目成绩,但可能存在重复的学生名称,比如同一学生有多条记录,比如不同学期的成绩。这时候,GROUP BY name会将同一学生的多条记录合并,计算总分时会将各科成绩相加,这可能不是用户想要的,因为每个记录已经包含了一个学生的所有科目成绩,所以合并后的总分可能是错误的。 例如,如果学生张三有两条记录,各科成绩分别为(语文80,数学70)(语文90,数学85),那么GROUP BY name后,SUM(chinese + math + ...)会将这两条记录的chinese相加(80+90=170),math相加(70+85=155),这可能不是用户想要的,因为每个记录代表的是不同的情况,比如不同学期,而总分应该是每个记录的总分,然后求所有记录总分的平均值?或者求每个学生总分(所有记录各科),然后求这些学生总分的平均值? 这需要明确表结构需求。但根据用户的问题,是“计算总分的平均值”,可能指的是每个学生总分的平均值。例如,每个学生有一个总分,然后求所有学生总分的平均。 在这种情况下,如果表中每个学生只有一条记录,那么直接使用AVG函数对各科求平均即可。例如: SELECT AVG(chinese + math + english + physics + chemistry) AS average_total_score FROM t_score; 但如果表中每个学生有多条记录,并且需要将这些记录合并计算每个学生总分,那么就需要使用子查询先按学生分组计算总分,再求平均: SELECT AVG(total_score) AS average_total_score FROM ( SELECT name, SUM(chinese + math + english + physics + chemistry) AS total_score FROM t_score GROUP BY name ) AS subquery; 因此,用户的问题的答案取决于表结构。但根据用户提供的引用[1]中的例子,他们的SQL语句使用了GROUP BY name,说明他们可能希望按学生分组,即使每个学生可能有多条记录。因此,正确的做法可能涉及子查询。 现在回到用户的问题,用户想计算总分的平均值,即每个学生总分,然后求这些总分的平均值。因此,正确的SQL应该使用子查询先计算每个学生总分,再求平均。 例如: SELECT AVG(total_score) AS average_total_score FROM ( SELECT name, SUM(chinese + math + english + physics + chemistry) AS total_score FROM t_score GROUP BY name ) AS subquery; 这样就能得到每个学生总分的平均值。 另外,用户提供的引用[2]中的例子,有一个stud表,结构为id, name, chinese, math, english。每个学生一条记录,各科成绩作为列。在这种情况下,如果用户想计算所有学生总分的平均,可以使用: SELECT AVG(chinese + math + english) AS average_total_score FROM stud; 但根据用户的问题,可能需要的是每个学生总分的平均,即每个学生总分各科,然后求这些总分的平均。在这种情况下,两种方法的结果相同,因为每个学生只有一条记录,所以子查询直接计算的结果是一样的。 因此,总结可能的解决方案: 1. 如果每个学生有多条记录(如不同科目或不同考试),需要先按学生分组计算总分,再求平均,使用子查询。 2. 如果每个学生只有一条记录,各科成绩作为列,则直接使用AVG函数对各科求平均。 根据用户提供的引用,可能情况是第二种,即各科作为列,每个学生一条记录。因此,正确的SQL可能是: SELECT AVG(chinese + math + english + physics + chemistry) AS average_total_score FROM t_score; 但需要确认用户的需求是否需要分组。例如,在引用[1]的示例中,他们使用GROUP BY name来计算每个学生总分平均分,可能意味着表中存在同一个学生的多条记录,需要合并。例如,可能表中每个学生每个科目有一条记录,各科成绩作为列,但每个学生有多个记录,每个记录对应不同的科目?这似乎不太可能,因为通常科目会作为行而不是列。例如,正确的表结构可能是学生科目、分数,而不是每个科目作为列。这种情况下,可能需要不同的处理方式。 但根据用户提供的引用中的SQL语句,例如引用[1]中的SUM(chinese + math + ...),这说明各科作为列存在。因此,可能每个学生对应一条记录,包含所有科目成绩。这时,GROUP BY name可能没有必要,除非存在重复的学生名字。但通常,学生应该用唯一标识如id来区分,而不是名字。因此,可能用户提供的引用中的例子存在设计问题,或者可能表中每个学生有多条记录,但各科成绩作为列,这可能不太合理。 在这种情况下,可能需要进一步澄清。但根据用户的问题,他们可能希望按学生分组计算总分,然后求平均,无论表结构如何。因此,给出两种可能的解决方案,并解释适用场景。 不过,用户的问题具体是“计算总分的平均值”,结合引用中的例子,可能他们需要的是先计算每个学生总分,再对这些总分求平均,不管表结构如何。因此,正确的SQL应使用子查询: SELECT AVG(total_score) AS average_total_score FROM ( SELECT SUM(chinese + math + english + physics + chemistry) AS total_score FROM t_score GROUP BY name ) AS subquery; 或者,如果表中每个学生只有一条记录,可以简化为: SELECT AVG(chinese + math + english + physics + chemistry) AS average_total_score FROM t_score; 但需要根据表结构决定。为了确保正确性,可能需要考虑两种情况,并在回答中说明。 最后,生成相关问题时,需要考虑用户可能进一步的问题,比如如何处理分组、是否存在重复学生、如何优化查询等。</think>要在MySQL中计算学生总分的平均值,需先计算每个学生各科总分,再对这些总分求平均值。以下是具体实现方法: ### 方法实现 假设表结构如下(以引用[1]的`t_score`表为例): ```sql CREATE TABLE t_score ( name VARCHAR(50), chinese INT, math INT, english INT, physics INT, chemistry INT ); ``` #### 步骤分解 1. **计算每个学生总分** 通过`SUM(chinese + math + english + physics + chemistry)`计算单行记录中各科成绩,并按学生分组: ```sql SELECT name, SUM(chinese + math + english + physics + chemistry) AS total_score FROM t_score GROUP BY name; ``` 2. **对总分求平均值** 将上述结果作为子查询,用`AVG(total_score)`计算总分的平均值: ```sql SELECT AVG(total_score) AS average_total_score FROM ( SELECT name, SUM(chinese + math + english + physics + chemistry) AS total_score FROM t_score GROUP BY name ) AS subquery; ``` #### 简化写法(单条语句) 若每个学生在表中仅有**一条记录**(无重复分组),可直接对单行总分求平均: ```sql SELECT AVG(chinese + math + english + physics + chemistry) AS average_total_score FROM t_score; ``` ### 关键点说明 - **分组必要性**:若表中存在同一学生的多条记录(如不同学期成绩),需用`GROUP BY`合并计算总分[^1]。 - **聚合函数**:`SUM`用于累加多列成绩,`AVG`用于计算平均值[^2]。 - **性能优化**:对大型数据集,可考虑为`name`列添加索引以加速分组操作[^3]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值