-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
(SELECT stu.*,IFNULL(s.`s_score`,0) AS ss,c.`c_id` FROM student stu INNER JOIN score s ON s.`s_id`=stu.s_id STRAIGHT_JOIN course c ON s.`c_id` = c.`c_id` AND c.`c_id` = '01' ORDER BY ss DESC LIMIT 2 OFFSET 1)
UNION ALL
(SELECT stu.*,IFNULL(s.`s_score`,0) AS ss ,c.`c_id` FROM student stu INNER JOIN score s ON s.`s_id`=stu.s_id STRAIGHT_JOIN course c ON s.`c_id` = c.`c_id` AND c.`c_id` = '02' ORDER BY ss DESC LIMIT 2 OFFSET 1)
UNION ALL
(SELECT stu.*,IFNULL(s.`s_score`,0) AS ss,c.`c_id` FROM student stu INNER JOIN score s ON s.`s_id`=stu.s_id STRAIGHT_JOIN course c ON s.`c_id` = c.`c_id` AND c.`c_id` = '03' ORDER BY ss DESC LIMIT 2 OFFSET 1)
-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT * FROM course s
INNER JOIN(SELECT s.`c_id`, ROUND(SUM(CASE WHEN s.`s_score` < 60 AND s.`s_score` >0 THEN 1 ELSE 0 END)/COUNT(*),2) 百分比 FROM score s GROUP BY s.`c_id`) t1 ON t1.c_id = s.`c_id`
INNER JOIN(SELECT s.`c_id`, ROUND(SUM(CASE WHEN s.`s_score` < 70 AND s.`s_score` >=60 THEN 1 ELSE 0 END)/COUNT(*),2) 百分比 FROM score s GROUP BY s.`c_id`)t2 ON t2.c_id = s.`c_id`
INNER JOIN (SELECT s.`c_id`, ROUND(SUM(CASE WHEN s.`s_score` < 85 AND s.`s_score` >=70 THEN 1 ELSE 0 END)/COUNT(*),2) 百分比 FROM score s GROUP BY s.`c_id`)t3 ON t3.c_id = s.`c_id`
INNER JOIN (SELECT s.`c_id`, ROUND(SUM(CASE WHEN s.`s_score` < 100 AND s.`s_score` >=85 THEN 1 ELSE 0 END)/COUNT(*),2) 百分比 FROM score s GROUP BY s.`c_id`)t4 ON t4.c_id = s.`c_id`
-- 24、查询学生平均成绩及其名次
SELECT stu.*,AVG(s.`s_score`),@r:=@r+1 排名 FROM score s INNER JOIN student stu ON stu.s_id = s.`s_id` INNER JOIN (SELECT @r := 0) t GROUP BY s.`s_id`
-- 25、查询各科成绩前三名的记录(--待完善)
SELECT * FROM (SELECT s.*,(SELECT COUNT(0)+1 FROM score s1 WHERE s1.`c_id` = s.`c_id` AND s1.`s_score` > s.`s_score` ) 排名
FROM score s) t WHERE top <= 3 ORDER BY t.`c_id`,top;
-- 26、查询每门课程被选修的学生数
SELECT c.`c_id`, c.`c_name`,COUNT(*) FROM score s,course c WHERE s.`c_id` = c.`c_id` GROUP BY s.`c_id`;
-- 27、查询出只有两门课程的全部学生的学号和姓名
SELECT stu.* FROM score s,student stu WHERE stu.`s_id` = s.`s_id` GROUP BY s.`s_id` HAVING COUNT(*) = 2;
-- 28、查询男生、女生人数
SELECT stu.`s_sex`,COUNT(*) FROM student stu GROUP BY stu.`s_sex`;
-- 29、查询名字中含有"风"字的学生信息
SELECT * FROM student s WHERE s.`s_name` LIKE '%风%';
-- 30、查询同名同性学生名单，并统计同名人数
SELECT * FROM student GROUP BY s_name HAVING COUNT(s_name) > 1;
-- 31、查询1990年出生的学生名单
SELECT * FROM student WHERE s_birth LIKE '1990%';
-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT course.*, AVG(s_score) s FROM score,course WHERE course.`c_id` = score.`c_id` GROUP BY score.c_id ORDER BY s DESC,score.`c_id` ASC;
-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT stu.*,AVG(s.`s_score`) average FROM score s INNER JOIN student stu ON stu.`s_id` = s.`s_id` GROUP BY s.`s_id` HAVING average>=85;
-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
SELECT stu.*,s.`s_score` FROM course c INNER JOIN score s ON s.`c_id` = c.`c_id` AND c.`c_name` = '数学' INNER JOIN student stu ON stu.`s_id` = s.`s_id` WHERE s.`s_score` < 60;
-- 35、查询所有学生的课程及分数情况；
SELECT stu.*
,SUM(CASE c.`c_name` WHEN '语文' THEN s.`s_score` ELSE 0 END ) 语文
,SUM(CASE c.`c_name` WHEN '数学' THEN s.`s_score` ELSE 0 END ) 数学
,SUM(CASE c.`c_name` WHEN '英语' THEN s.`s_score` ELSE 0 END ) 英语
,SUM(s.s_score) 总分
FROM student stu LEFT JOIN score s ON s.`s_id` = stu.`s_id` INNER JOIN course c ON c.`c_id` = s.`c_id` GROUP BY stu.`s_id`;
-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
SELECT * FROM student stu LEFT JOIN score s ON stu.`s_id` = s.`s_id` WHERE s.`s_score` > 70
-- 37、查询不及格的课程
SELECT * FROM score s JOIN course c ON s.`c_id` = c.c_id WHERE s.`s_score` < 60;
-- 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
SELECT stu.* FROM score s INNER JOIN student stu WHERE s.`c_id` = '01' AND s.`s_score` > 80;
-- 39、求每门课程的学生人数
SELECT COUNT(*) FROM score s GROUP BY s.`c_id`
-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT stu.*,MAX(s.`s_score`) FROM course c INNER JOIN teacher t ON t.t_id = c.t_id AND t.t_name = '张三'
INNER JOIN score s ON s.`c_id` = c.c_id INNER JOIN student stu ON stu.s_id = s.`s_id`
