mysql统计－关于学生成绩

来源：互联网发布：java程序员认证培训编辑：程序博客网时间：2024/05/01 00:02

<span style="color: rgb(255, 0, 0); font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);">注意前5个问题使用张三和李四两个字段</span>

1.计算每个人的总成绩并排名(要求显示字段：姓名，总成绩)

SELECT name,SUM(score)from stuscoreGROUP BY `name`ORDER BY SUM(score) ASC

2. 计算每个人的总成绩并排名(要求显示字段: 学号，姓名，总成绩)

SELECT stuid,`name`,SUM(score)FROM stuscoreGROUP BY `name`ORDER BY SUM(score)

stuid

name

SUM(score)

1张三2392李四240

3. 计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)

写法1

SELECT stuid,`name`,`subject`,max(score)FROM stuscoregroup by stuid

写法2：

SELECT t1.stuid,t1.`name`,t1.`subject`,t1.scoreFROM stuscore t1,(SELECT `stuid`,MAX(score) AS maxscore FROM stuscore GROUP BY `stuid`) t2where t1.stuid=t2.stuid and t1.score=t2.maxscore

stuidnamesubjectscore1张三数学892李四数学90

4. 计算每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）

SELECT stuid,`name`,AVG(score)FROM stuscoregroup by stuid

stuidname

AVG(score)

1张三79.66672李四80.0000

5. 列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩)

方法一：

SELECT stuid,`name`,`subject`,max(score)FROM stuscore GROUP BY `subject`

方法二：

SELECT t1.stuid,t1.`name`,t1.`subject`,t2.maxscoreFROM stuscore t1,(SELECT `subject`,MAX(score)as maxscore FROM stuscore GROUP BY `subject`)t2WHERE t1.`subject`=t2.`subject` and t1.score=t2.maxscore

stuidnamesubjectmaxscore1张三语文802李四数学902李四英语80

从问题6开始增加王五字段

6. 列出各门课程成绩最好的两位学生(要求显示字段: 学号，姓名,成绩)

select stuid,`name`,SUM(score)as sumscorefrom stuscore GROUP BY `name`order by sumscore DESCLIMIT 2

stuidnamesumscore3王五2682李四240
7.列出数学成绩的排名

select `name`,`subject`,scorefrom stuscorewhere `subject`="数学"ORDER BY score DESC

8.求出李四的数学成绩的排名

9.统计学科成绩及格，良，优的个数

select subject, (select count(*) from stuscore where score<60 and subject=t1.subject) as 不及格,(select count(*) from stuscore where score between 60 and 80 and subject=t1.subject) as 良,(select count(*) from stuscore where score >80 and subject=t1.subject) as 优from stuscore t1                                                                                                                   <span style="font-family: 宋体;">group by subject</span>

subject及格良优数学003英语021语文02110.学号姓名语文数学英语总分平均分

</pre><pre name="code" class="sql">select stuid as 学号,name as 姓名,sum(case when subject='语文' then score else 0 end) as 语文,sum(case when subject='数学' then score else 0 end) as 数学,sum(case when subject='英语' then score else 0 end) as 英语,sum(score) as 总分,(sum(score)/count(*)) as 平均分from stuscoregroup by stuid,name

2 0