MySQL练习题

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

SELECT stuid,name,sum(score) AS sum 
FROM stuscore
GROUP BY name
ORDER BY sum;

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

SELECT t1.stuid,t1.name,t1.subject,t1.score 
FROM stuscore t1,
(SELECT stuid,MAX(score) AS maxscore 
FROM stuscore GROUP BY stuid) t2 
WHERE t1.stuid = t2.stuid AND t1.score = t2.maxscore;

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

SELECT t1.stuid,t1.name,t1.subject,t2.maxsource
FROM stuscore AS t1,
(SELECT subject,max(score) AS maxsource
FROM stuscore
GROUP BY subject) AS t2
WHERE t1.subject = t2.subject AND t1.score = t2.maxsource;

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

SELECT stuid,name,avg(score)
FROM stuscore
GROUP BY name;

SELECT stuid,name,ROUND(avg(score), 0)
FROM stuscore
GROUP BY name;

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

    (1)如果考虑例如有两个同分数的第二名可以用下面的语句：

SELECT a.name, a.subject, a.score FROM stuscore AS a WHERE 
(SELECT COUNT(DISTINCT score) FROM stuscore AS b 
WHERE b.subject = a.subject AND b.score >= a.score) <= 2 
ORDER BY a.subject ASC, a.score DESC

    (2)如果有同分数的两个第二名则删除第二名：

SELECT a.name, a.subject, a.score FROM stuscore AS a WHERE
(SELECT COUNT(*) FROM stuscore AS b
WHERE b.subject = a.subject AND b.score >= a.score) <= 2
ORDER BY a.subject ASC, a.score DESC

6.列出各学生各课程成绩、总分、平均分：

SELECT t1.stuid AS 学号,t1. NAME AS 姓名,t1. SUBJECT AS 课程,t1.score AS 分数,
(SELECT avg(t2.score)
FROM stuscore AS t2
WHERE t1.stuid = t2.stuid) AS 平均分,
(SELECT sum(t3.score)
FROM stuscore AS t3
WHERE t1.stuid = t3.stuid) AS 总分
FROM stuscore AS t1;

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 stuscore
GROUP BY stuid,name
ORDER BY 总分 DESC;

7.列出数学成绩的排名

SELECT stuid,name,subject,score ,@rank := @rank + 1 AS rank
FROM stuscore AS t1,
(SELECT @rank := 0) AS 排名
WHERE subject = '数学'
ORDER BY score DESC;

8.列出各门课程的平均成绩

SELECT subject,avg(score)
FROM stuscore
GROUP BY subject;

9.列出数学成绩在2-3名的学生：

SELECT stuid,name,subject,score
FROM stuscore
WHERE subject = '数学'
ORDER BY score DESC
LIMIT 1,2;

10.查询李四的数学成绩排名：

SELECT *
FROM
(SELECT stuid,name,subject ,@rank := @rank + 1 AS rank
FROM stuscore,(SELECT @rank := 0) AS 排名
WHERE SUBJECT = '数学'
ORDER BY score DESC) AS t1
WHERE NAME = '李四';

11.查询课程不及格、良、优的个数：

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
GROUP BY subject;

12.查询各课程各学生分数情况：

SELECT subject,
sum(CASE WHEN NAME = '张三' THEN score ELSE 0 END) AS 张三,
sum(CASE WHEN NAME = '李四' THEN score ELSE 0 END) AS 李四,
sum(CASE WHEN NAME = '王五' THEN score ELSE 0 END) AS 王五
FROM stuscore
GROUP BY subject;

13.计算3门课都及格的人的平均成绩：

SELECT name,AVG(score) AS avgscore FROM stuscore a
WHERE
(SELECT SUM(CASE WHEN b.score>=60 THEN 1 ELSE 0 END) FROM stuscore b WHERE b.name=a.name)=3
GROUP BY name;

 

 

 

参考博客：https://blog.csdn.net/mayanyun2013/article/details/50845667