![](https://i-blog.csdnimg.cn/blog_migrate/8cf92d063f74be88c269e5c858e37603.png)
1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号(重点)
SELECT a.s_id,a.s_score FROM
(SELECT * FROM score WHERE c_id='01') as a
INNER JOIN
(SELECT * FROM score WHERE c_id='02') as b
on a.s_id=b.s_id
WHERE a.s_score>b.s_score;
15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩(重点)
SELECT a.s_id,a.s_name,avg(s_score) FROM student as a
INNER JOIN score as b
ON a.s_id=b.s_id
WHERE a.s_id IN(
SELECT s_id FROM score
WHERE s_score<60 GROUP BY s_id HAVING COUNT(DISTINCT c_id)>=2
)
GROUP BY a.s_id,s_name
16、检索"01"课程分数小于60,按分数降序排列的学生信息
# 方法一:个人
SELECT * FROM student
INNER JOIN
(SELECT s_id,s_score FROM score WHERE c_id=01 AND s_score<60) as t
on student.s_id=t.s_id
ORDER BY s_score DESC;
# 方法二:个人
SELECT * FROM student
INNER JOIN score ON student.s_id=score.s_id
WHERE s_score<60 AND c_id=01
ORDER BY s_score DESC;
# 方法三:
SELECT * FROM student as t
INNER JOIN score as s on t.s_id=s.s_id
WHERE s.c_id=01 AND s.s_score<60
ORDER BY s.s_score DESC ;
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重重点与35一样)
SELECT s_id "学号",
max(CASE WHEN c_id=01 THEN s_score ELSE NULL END ) "语文",
max(CASE WHEN c_id=02 THEN s_score ELSE NULL END ) "数学",
max(CASE WHEN c_id=03 THEN s_score ELSE NULL END ) "英语",
avg(s_score) "平均成绩" FROM score
GROUP BY s_id
ORDER BY avg(s_score) DESC
18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
SELECT s.c_id "课程ID",c.c_name "课程名字",
max(s.s_score) "最高分",
min(s.s_score)"最低分",
avg(s.s_score)"平均分",
sum(CASE WHEN s.s_score>=60 THEN 1 ELSE 0 END )/count(s_id) "及格",
sum(CASE WHEN s.s_score>=70 AND s.s_score<80 THEN 1 ELSE 0 END )/count(s_id) "中等",
sum(CASE WHEN s.s_score>=80 AND s.s_score<90 THEN 1 ELSE 0 END )/count(s_id)"优良",
sum(CASE WHEN s.s_score>=90 THEN 1 ELSE 0 END )/count(s_id) "优秀"
FROM course AS c
INNER JOIN
score as s ON c.c_id=s.c_id
GROUP BY c.c_id
SELECT c_id,
avg(CASE WHEN s_score >= 0 AND s_score < 60 THEN 1.0 ELSE 0.0 END) "及格率",
avg(CASE WHEN s_score >= 60 AND s_score < 70 THEN 1.0 ELSE 0.0 END) "中等率",
avg(CASE WHEN s_score >= 70 AND s_score < 85 THEN 1.0 ELSE 0.0 END) "良好率",
avg(CASE WHEN s_score >= 85 AND s_score < 100 THEN 1.0 ELSE 0.0 END) "优秀率"
FROM score
GROUP BY c_id;
19、按各科成绩进行排序,并显示排名(重点row_number)
窗口函数可以进行排序,生成序列号等一般的聚合函数无法实现的高级操作。
窗口函数大体可以分为以下两种:
1.能够作为窗口函数的聚合函数(sum,avg,count,max,min)
2.rank,dense_rank,row_number等专用窗口函数。
语法的基本使用方法:使用rank函数
rank函数是用来计算记录排序的函数
https://blog.csdn.net/qq_41805514/article/details/81772182
专用函数的种类:1.rank函数:计算排序时,如果存在相同位次的记录,则会跳过之后的位次。
2.dense_rank函数:同样是计算排序,即使存在相同位次的记录,也不会跳过之后的位次。
3.row_number函数:赋予唯一的连续位次。
![](https://i-blog.csdnimg.cn/blog_migrate/5751dc7e2c70e850baa303585e5dd260.png)
23、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩,分别统计各分数段人数:课程ID和课程名称(重点和18题类似)
SELECT c.c_id "课程ID",c_name "课程名字",
sum(CASE WHEN s_score<=100 and s_score>85 THEN 1 ELSE 0 END ) AS "[100,85]",
sum(CASE WHEN s_score<=85 and s_score>70 THEN 1 ELSE 0 END ) "[85,70]",
sum(CASE WHEN s_score<=70 and s_score>60 THEN 1 ELSE 0 END )"[70,60]",
sum(CASE WHEN s_score<=60 THEN 1 ELSE 0 END )"<60"
FROM score as s
INNER JOIN course as c on s.c_id=c.c_id
GROUP BY c.c_id,c.c_name;
SELECT c.c_id "课程ID",c_name "课程名字",
count(CASE WHEN s_score<=100 and s_score>85 THEN 1 ELSE NULL END ) AS "[100,85]",
count(CASE WHEN s_score<=85 and s_score>70 THEN 1 ELSE NULL END ) "[85,70]",
count(CASE WHEN s_score<=70 and s_score>60 THEN 1 ELSE NULL END )"[70,60]",
count(CASE WHEN s_score<=60 THEN 1 ELSE NULL END )"<60"
FROM score as s
INNER JOIN course as c on s.c_id=c.c_id
GROUP BY c.c_id,c.c_name;
24、查询学生平均成绩及其名次(同19题,重点)
SELECT s_id,avg(s_score),
rank() OVER (ORDER BY avg(s_score)) as ranking FROM score
group by s_id
# 不可加partition by,需使用group by
25、查询各科成绩前三名的记录(不考虑成绩并列情况)(重点 与22题类似)
26、查询每门课程被选修的学生数(不重点)
26、查询每门课程被选修的学生数(不重点)
SELECT c_name,COUNT(c.c_id)
FROM score as s
INNER JOIN
course as c on s.c_id=c.c_id
GROUP BY c_name;
# 注:一般需要将select后的字段放在group by后面
SELECT c.c_id,c.c_name,count(DISTINCT s.s_id) AS "数量"
FROM score as s
INNER JOIN
course as c on s.c_id=c.c_id
GROUP BY c.c_id,c.c_name;
27、 查询出只有两门课程的全部学生的学号和姓名(不重点)
# 方法一:
SELECT s.s_id,s.s_name
FROM student AS s
INNER JOIN
score as sc on s.s_id=sc.s_id
GROUP BY s.s_id HAVING count(DISTINCT c_id)=2;
# 错误
SELECT s.s_name,s.s_id
FROM student AS s
INNER JOIN
score as sc on s.s_id=sc.s_id
WHERE count(DISTINCT c_id)=2;
# 方法二:
SELECT s_id,s_name FROM student
WHERE s_id in(SELECT s_id FROM score GROUP BY s_id
HAVING count(DISTINCT c_id)=2);
28、查询男生、女生人数(不重点)
# 方法一:
SELECT s_sex,count(s_sex) FROM student
GROUP BY s_sex;
# 方法二:
SELECT
sum(CASE WHEN s_sex='男' THEN 1 ELSE 0 END ) "男生人数",
sum(CASE WHEN s_sex='女' THEN 1 ELSE 0 END ) "女生人数"
FROM student
# 方法三:
SELECT
count(CASE WHEN s_sex='男' THEN 1 ELSE NULL END ) "男生人数",
count(CASE WHEN s_sex='女' THEN 1 ELSE NULL END ) "女生人数"
FROM student
# 注:null对count来说是不计算个数的,所以后面不能写0,只能用null
35、查询所有学生的课程及分数情况(重点)
# 不对
SELECT s.s_id,s.s_name,c.c_name,sc.s_score
FROM student as s
INNER JOIN score as sc
ON s.s_id=sc.s_id
INNER JOIN course as c
ON c.c_id=sc.c_id
GROUP BY s.s_id,s.s_name
# 正确做法:(行转列)
SELECT s.s_id,s.s_name,
max(CASE WHEN c.c_name="语文" THEN s_score ELSE NULL END ) AS "语文",
max(CASE WHEN c.c_name="数学" THEN s_score ELSE NULL END ) AS "数学",
max(CASE WHEN c.c_name="英语" THEN s_score ELSE NULL END ) AS "英语"
FROM student as s
LEFT JOIN score as sc
ON s.s_id=sc.s_id
LEFT JOIN course as c
ON c.c_id=sc.c_id
GROUP BY s.s_id,s.s_name
![](https://i-blog.csdnimg.cn/blog_migrate/ad3cfadc45c361af575cdd04e9c51dad.png)
![](https://i-blog.csdnimg.cn/blog_migrate/d7cfb67a815578d09f7c0fa5c0a618a7.png)
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数(重点)
# 错误处理方法:
SELECT s.s_name,c.c_name,sc.s_score
FROM student as s
INNER JOIN score as sc on s.s_id=sc.s_id
INNER JOIN course AS c ON c.c_id=sc.c_id
GROUP BY s.s_id HAVING sc.s_score>70;
# 正确处理方法:
SELECT s.s_name,c.c_name,sc.s_score
FROM student as s
INNER JOIN score as sc on s.s_id=sc.s_id
INNER JOIN course AS c ON c.c_id=sc.c_id
WHERE sc.s_score>70;
# 注:where 后面要跟的是数据表里的字段,where针对数据库文件的发挥作用,
# 而having只是根据前面查询出来的结果集再次进行查询,因此having是针对结果集发挥作用。
40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩(重要top)
SELECT s.s_id,s.s_name,c.c_name,c.c_id,sc.s_score
FROM student as s
INNER JOIN score as sc
ON s.s_id=sc.s_id
INNER JOIN course as c
ON sc.c_id=c.c_id
INNER JOIN teacher as t
ON t.t_id=c.t_id
WHERE t.t_name="张三" ORDER BY sc.s_score DESC limit 0,1; # 0表示从0开始取,若为降序排列则0是第一位,1表示取几条
# SQL SERVER 中用top
/*
SELECT top 1 s.s_id,s.s_name,c.c_name,c.c_id
FROM student as s
INNER JOIN score as sc
ON s.s_id=sc.s_id
INNER JOIN course as c
ON sc.c_id=c.c_id
INNER JOIN teacher as t
ON t.t_id=c.t_id
WHERE t.t_name="张三" ORDER BY DESC
*/