MySQL Lesson 9: SQL面试50-Part3

1.查询各科成绩列出 课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 (70中80良90优) 

SELECT a.C_id as '课程ID',Course.C_name as '课程名称',
  max(a.Score) as '最高分',min(a.Score) as '最低分',avg(a.Score) as '平均分',
  (sum(pass)*100/count(*))||'%' as '及格率',
  (sum(med)*100/count(*))||'%' as '中等率',
  (sum(liang)*100/count(*))||'%' as '优良率',
  (sum(excellent)*100/count(*))||'%' as '优秀率'
FROM (SELECT *,
        CASE WHEN Score>=60 THEN 1 ELSE 0 END AS pass,
        CASE WHEN Score>=70 AND Score<80 THEN 1 ELSE 0 END AS med,
        CASE WHEN Score>=80 AND Score<90 THEN 1 ELSE 0 END AS liang,
        CASE WHEN Score>=90 THEN 1 ELSE 0 END AS excellent
      FROM Score )a
LEFT JOIN Course ON a.C_id = Course.C_id
GROUP BY a.C_id 

补充：

MySQL语句中CAST(AVG(b.score) AS DECIMAL(5,2)

其中Cast的函数用于转换类型。
Cast(字段名 as 转换的类型 )。

decimal(5,2)中的“2”表示小数部分的位数，如果插入的值未指定小数部分或者小数部分不足两位则会自动补到2位小数，若插入的值小数部分超过了2为则会发生截断，截取前2位小数。
“5”指的是整数部分加小数部分的总长度，也即插入的数字整数部分不能超过“5-2”位，否则不能成功插入，会报超出范围的错误。
————————————————
版权声明：本文为优快云博主「invers3」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.youkuaiyun.com/tan1123y/article/details/98474840

2.按各科成绩进行排序

SELECT C_id,sum(Score)
FROM Score
GROUP BY C_id
ORDER BY sum(Score) DESC   

 3.查询学生的总成绩并进行排序

SELECT S_id,sum(Score)
FROM Score
GROUP BY S_id
ORDER BY sum(Score) DESC 

4.查询不同老师所教不同课程平均分从高到低显示

同时按老师和课程分组

SELECT T_id,T_name,C_id,avg(Score)
FROM Total
GROUP BY T_id,C_id 
ORDER BY avg(Score) DESC   

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

SELECT C_id as'课程编号',C_name as '课程名称', 
  sum(a) as '人数1',sum(b) as '人数2',sum(c) as '人数3',sum(d) as '人数4',
  (sum(a)*100/count(*))||'%' as '百分比1', 
  (sum(b)*100/count(*))||'%' as '百分比2', 
  (sum(c)*100/count(*))||'%' as '百分比3', 
  (sum(d)*100/count(*))||'%' as '百分比4'   
FROM (SELECT *,
      CASE WHEN Score>=85 AND Score<=100 THEN 1 ELSE 0 END as a,
      CASE WHEN Score>=70 AND Score<=84 THEN 1 ELSE 0 END as b,
      CASE WHEN Score>=60 AND Score<=69 THEN 1 ELSE 0 END as c,
      CASE WHEN Score<=59 THEN 1 ELSE 0 END as d 
     FROM Total)
GROUP BY C_id   

6.查询学生平均成绩

SELECT S_id,avg(Score)
FROM Score 
GROUP BY S_id  

7.查询各科成绩前三名的记录？

思路：

select * from movies as a where (a.x = b.y xxx)；

本题可以用这个思路，计算每一个学生的信息，假设为a

然后再后面的复杂计算里每一行计算，如果 a的成绩和其他人比较，比a成绩高的人数小于3，那么a就是前3名，留下。

SELECT a.C_id,a.S_id,a.Score 
FROM Score a 
WHERE (select count(b.s_id) from score b 
where a.c_id=b.c_id and a.score< b.score)< 3 
GROUP BY a.c_id,a.s_id;

8.查询每门课程被选修的学生数

SELECT C_id,count(*)
FROM Score
GROUP BY C_id