mysql从大到小排序_SQL面试50题——学习记录（三）

以下是20-30题，但是有几题没得了，我也就没去再找。

#第20题：查询学生的总成绩并进行排名

对学生总成绩从大到小排序

SELECT stu.s_id 学号, stu.s_name 姓名

,SUM(s.s_score) 总成绩,rank () over (ORDER BY SUM(s.s_score) DESC) 排名

FROM student stu

JOIN score s ON stu.s_id = s.s_id

GROUP BY stu.s_id;

#执行结果如下

image

#第21题: 查询不同老师所教不同课程平均分从高到低显示 (这题的题目有点迷糊，我就按照自己的理解来写了)

SELECT s.c_id 课程号, c.c_name 课程名, t.t_name 教师, AVG(s.s_score) 平均分,rank()over( ORDER BY AVG(s.s_score) DESC ) 排名

FROM score s

JOIN course c ON s.c_id = c.c_id

JOIN teacher t ON c.t_id = t.t_id

GROUP BY s.c_id;

#执行结果如下

image

#第22题：查询所有课程的成绩第2名到第3名的学生信息及课程成绩

SELECT * FROM (SELECT s.s_id 学号,s_name 姓名,s.c_id 课程号,c_name 课程名,s_score 分数,rank()over(PARTITION BY s.c_id ORDER BY s.s_score DESC)名次

FROM score s

JOIN course c ON s.`c_id`=c.`c_id`

JOIN student stu ON stu.`s_id`=s.`s_id`)AS a

WHERE 名次=2 OR 名次=3 ;

#执行结果如下

image

/*

此题总结：题目对第二名与第三名的要求不明确，暂且暂定是按照rank()的形式来进行排名，当rank后的序号为2则即为第二名，为3即为第三名。

这种定义可能在实际业务中是不合理的，此处忽略。

这题与21题基本上是类似的，无非就是对排序后的结果再次筛选罢了。

本题我有2个错误点

1：没有给衍生表起别名，导致报错

如下：SELECT * FROM (SELECT s.s_id 学号,s_name 姓名,s.c_id 课程号,c_name 课程名,s_score 分数,rank()over(PARTITION BY s.c_id ORDER BY s.s_score DESC)名次

FROM score s

JOIN course c ON s.c_id=c.c_id

JOIN student stu ON stu.s_id=s.s_id) 此处少了为衍生表取别名(as a)

WHERE 名次=2 OR 名次=3 ;

2: 衍生表内字段有重复，导致报错，因为衍生表是多边联合查询来的，字段有重复的，当把其作为衍生表再进行查询时，里面有重复字段会报错。

SELECT * FROM (SELECT *,rank()over(PARTITION BY s.c_id ORDER BY s.s_score DESC)名次

FROM score s

JOIN course c ON s.c_id=c.c_id

JOIN student stu ON stu.s_id=s.s_id)AS a

WHERE 名次=2 OR 名次=3 ;

上述sql语句执行时会报错如下：

Duplicate column name 'c_id'。

所以需要把第二个select 后的 * 换成具体的字段名且不可重复

*/

#第23题：使用分数段[0,60),[60,70),(70,85),[85,100]来统计各科成绩，分别统计各分数段人数、课程ID和课程名称

SELECT s.c_id 课程号,c_name 课程名,

SUM(CASE WHEN s_score>=0 AND s_score<60 THEN 1 ELSE 0 END ) "[0,60)人数",

SUM(CASE WHEN s_score>=60 AND s_score<70 THEN 1 ELSE 0 END ) "[60,70)人数",

SUM(CASE WHEN s_score>=70 AND s_score<85 THEN 1 ELSE 0 END) "[70,85)人数",

SUM(CASE WHEN s_score>=85 AND s_score<=100 THEN 1 ELSE 0 END) "[85,100]人数"

FROM score s JOIN course c ON s.`c_id`=c.`c_id`

GROUP BY s.c_id

#执行结果如下

image

/*

此题与第18题解法类似

我做这题犯了2个比较蠢的错误

1.case when then end 忘记在结尾写end

2.为列取别名时，直接用了[0,60)人数,[60,70)人数... 没加双引号导致语法报错

顺便补充下mysql的列与表的取别名 需要注意的点

①：表取别名时，加双引号,就会报错

②：列取别名,无双引号的别名加空格会报错

③：列取别名,无双引号的别名加运算符号，逗号，括号等等会报错(但是加书名号这种不会报错，反正带符号的别名还是加上双引号为好)

*/

#第24题：查询学生平均成绩及其名次 (与第19题类似)

SELECT s.s_id 学号,s_name 姓名,ROUND(AVG(s_score),2) 平均成绩

,rank()over(ORDER BY AVG(s_score) DESC) 排名

FROM student stu

JOIN score s ON stu.s_id = s.s_id

GROUP BY s.s_id;

#执行结果如下：

image

第25题: 题目又没了，所以直接跳到26题

第26题：查询每门课程被选修的学生数

SELECT c_id ,COUNT(DISTINCT s_id) FROM score

GROUP BY c_id

第27题：查询出只有两门课程的全部学生的学号和姓名

SELECT s.s_id,stu.s_name

FROM score s JOIN student stu ON s.s_id = stu.s_id

GROUP BY s.s_id

HAVING COUNT(DISTINCT s.c_id)=2;

第28题：查询男生、女生人数

SELECT s_sex 性别,COUNT(s_id) 人数 FROM student

GROUP BY s_sex ;

image

SELECT SUM(CASE WHEN s_sex ="男" THEN 1 ELSE 0 END) 男生人数

,SUM(CASE WHEN s_sex ="女" THEN 1 ELSE 0 END) 女生人数

FROM student;

image

第29题: 查询名字中含有“风”字的学生信息

SELECT * FROM student

WHERE s_name LIKE "%风%";

第30题：题目也没有 ......