mysql 练习

原文地址 https://cloud.tencent.com/developer/article/1604980

对某些题可以精简

-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

SELECT Course.c_id, Course.c_name, MAX(Score.s_score), MIN(Score.s_score), AVG(Score.s_score) ,

(SELECT COUNT(1) FROM Score WHERE s_score >= 60 AND Score.c_id = Course.c_id)/(SELECT COUNT(1) FROM Score WHERE Score.c_id = Course.c_id) AS '及格率',

(SELECT COUNT(1) FROM Score WHERE s_score >= 70 AND Score.c_id = Course.c_id)/(SELECT COUNT(1) FROM Score WHERE Score.c_id = Course.c_id) AS '中等率',

(SELECT COUNT(1) FROM Score WHERE s_score >= 80 AND Score.c_id = Course.c_id)/(SELECT COUNT(1) FROM Score WHERE Score.c_id = Course.c_id) AS '优良率',

(SELECT COUNT(1) FROM Score WHERE s_score >= 90 AND Score.c_id = Course.c_id)/(SELECT COUNT(1) FROM Score WHERE Score.c_id = Course.c_id) AS '优秀率'

FROM Course LEFT JOIN Score ON

Course.c_id = Score.c_id

GROUP BY Course.c_id

-- 19、按各科成绩进行排序，并显示排名

select c1.s_id,c1.c_id,c1.c_name,@score:=c1.s_score,@i:=@i+1 from (select c.c_name,sc.* from course c

left join score sc on sc.c_id=c.c_id

where c.c_id="01" order by sc.s_score desc) c1 ,

(select @i:=0) a

union all

select c2.s_id,c2.c_id,c2.c_name,c2.s_score,@ii:=@ii+1 from (select c.c_name,sc.* from course c

left join score sc on sc.c_id=c.c_id

where c.c_id="02" order by sc.s_score desc) c2 ,

(select @ii:=0) aa

union all

select c3.s_id,c3.c_id,c3.c_name,c3.s_score,@iii:=@iii+1 from (select c.c_name,sc.* from course c

left join score sc on sc.c_id=c.c_id

where c.c_id="03" order by sc.s_score desc) c3

//或者

SELECT a.*,@i:=if(@n=a.c_name,@i+1,1) as '名次', (@n:=a.c_name) FROM

(SELECT Student.s_name, Student.s_id, Course.c_name,Score.s_score FROM Student

INNER JOIN Score ON Student.s_id = Score.s_id

LEFT JOIN Course ON Score.c_id = Course.c_id

ORDER BY Course.c_id,Score.s_score) a

--22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

SELECT Course.c_name, Course.c_id,Student.s_name, Score.s_id,Score.s_score FROM Student

INNER JOIN Score ON Score.s_id = Student.s_id

INNER JOIN Course ON Score.c_id = Course.c_id

WHERE (SELECT COUNT(1) FROM Score b WHERE b.c_id = Score.c_id AND b.s_score >= Score.s_score) IN (2,3)

ORDER BY Score.c_id

-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

SELECT Course.c_id, Course.c_name,

(SELECT COUNT(1) FROM Score WHERE Score.s_score < 60 AND Score.c_id = Course.c_id) / (SELECT COUNT(1) FROM Score WHERE Score.c_id = Course.c_id) AS '0-60',

(SELECT COUNT(1) FROM Score WHERE Score.s_score >= 60 AND Score.s_score < 70 AND Score.c_id = Course.c_id) / (SELECT COUNT(1) FROM Score WHERE Score.c_id = Course.c_id) AS '60-70',

(SELECT COUNT(1) FROM Score WHERE Score.s_score < 85 AND Score.s_score >= 70 AND Score.c_id = Course.c_id) / (SELECT COUNT(1) FROM Score WHERE Score.c_id = Course.c_id) AS '70-85',

(SELECT COUNT(1) FROM Score WHERE Score.s_score >= 85 AND Score.c_id = Course.c_id) / (SELECT COUNT(1) FROM Score WHERE Score.c_id = Course.c_id) AS '85-100'

FROM Course

-- 24、查询学生平均成绩及其名次 

set @i=0;

select a.*,@i:=@i+1 from (SELECT Student.s_id, Student.s_name, AVG( Score.s_score) FROM Student LEFT JOIN Score ON

Student.s_id = Score.s_id GROUP BY Student.s_id ORDER BY AVG( Score.s_score) DESC) a. //为什么要执行两次select , 因为order by在select后面执行，会导致名次不正确

-- 25、查询各科成绩前三名的记录

SELECT Course.c_name, Course.c_id, Score.s_id,Score.s_score FROM Score LEFT JOIN Course ON Score.c_id = Course.c_id

WHERE (SELECT COUNT(1) FROM Score b WHERE b.c_id = Score.c_id AND b.s_score >= Score.s_score) <= 3 ORDER BY Score.c_id

--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数

SELECT Student.s_name, Course.c_name , Score.s_score FROM Student

INNER JOIN Score ON Student.s_id = Score.s_id AND Score.s_score > 70

INNER JOIN Course ON Course.c_id = Score.c_id

-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 

SELECT Student.s_id, Student.s_name,Score.c_id,Score.s_score FROM Student

LEFT JOIN Score ON Score.s_id = Student.s_id

LEFT JOIN Course ON Course.c_id = Score.c_id

WHERE (

SELECT COUNT(1) FROM Student st2

LEFT JOIN Score sc2 ON sc2.s_id = st2.s_id

LEFT JOIN Course c2 ON c2.c_id = sc2.c_id

WHERE sc2.s_score = Score.s_score AND c2.c_id != Course.c_id AND Student.s_id = st2.s_id

) > 1

-- 42、查询每门功成绩最好的前两名

SELECT Score.s_id,Score.c_id,Score.s_score FROM Score

WHERE (SELECT COUNT(1) FROM Score b WHERE b.c_id = Score.c_id AND b.s_score >= Score.s_score) <= 2 ORDER BY Score.c_id