Mysql练习(三)

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

SELECT c.*,MAX(s.`s_score`),MIN(s.`s_score`),ROUND(AVG(s.`s_score`),2)AS 平均成绩,
ROUND(100*(SUM(CASE WHEN s.s_score >= 60 THEN 1 ELSE 0 END ))/COUNT(1),2) AS 及格率,
ROUND(100*(SUM(CASE WHEN s.s_score BETWEEN 70 AND 80 THEN 1 ELSE 0 END ))/COUNT(1),2) 中等,
ROUND(100*(SUM(CASE WHEN s.s_score BETWEEN 80 AND 90 THEN 1 ELSE 0 END ))/COUNT(1),2) AS 优良,
ROUND(100*(SUM(CASE WHEN s.s_score >= 90 THEN 1 ELSE 0 END ))/COUNT(1),2) AS 优秀
FROM score s INNER JOIN course c ON s.`c_id` = c.`c_id` GROUP BY s.`c_id`;

-- 19、按各科成绩进行排序，并显示排名
-- mysql没有rank函数

SELECT t.*,@a:=@a+1 AS 排名 FROM (SELECT * FROM score s GROUP BY s_id,c_id,s_score ORDER BY s.`s_score` DESC) t,(SELECT @a:=0) a;

-- 20、查询学生的总成绩并进行排名

SELECT * FROM student stu ,(SELECT s_id,SUM(s_score)总分 FROM score GROUP BY s_id ORDER BY 总分 DESC) AS tmp WHERE stu.s_id = tmp.s_id;

-- 21、查询不同老师所教不同课程平均分从高到低显示

SELECT t.`t_name`,c.`c_name`,ROUND(AVG(s_score),2) 各科平均成绩 FROM score s INNER JOIN course c ON c.`c_id` = s.`c_id` 
INNER JOIN teacher t ON t.`t_id` = c.`c_id` GROUP BY c.`c_id` ORDER BY 各科平均成绩 DESC