练习31-35：多表关联查询、多条件自连接查询、子查询、窗口函数等

四张表信息

31. 查询课程编号为01且课程成绩在80分及以上的学生的学号和姓名

第一步：查询成绩表中01课程且分数在80分以上的记录

 SELECT 
 * 
 FROM sc
 WHERE cid = 01 AND score >= 80;
1
2
3
4

第二步：关联学生表，获取学生姓名，并从关联结果中筛选出题目要求的字段

 SELECT 
 a.sid,
 b.sname,
 a.score
 FROM (SELECT * FROM sc WHERE cid = 01 AND score >= 80) a
 JOIN student b
 ON a.sid = b.sid;
1
2
3
4
5
6
7

32. 求每门课程的学生人数

简单的聚合查询

 SELECT 
 cid,
 COUNT(*) AS ct
 FROM sc
 GROUP BY cid;
1
2
3
4
5

33. 假设成绩不重复，查询选修【张三】老师所授课程的学生中，成绩最高的学生信息及其成绩

关键词：张三老师所授课程、成绩最高、学生信息
第一步：从关键词分析，可见需要用到所有的四张表
第二步：查出张三老师教授了哪些课

 SELECT cid FROM teacher t JOIN course c ON t.`TId` = c.`TId` WHERE t.`Tname` = '张三';
1

第三步：从成绩表中过滤出张三老师所教授课程的成绩记录，由于成绩不重复，所以排序后limit 1就是成绩最高的记录

SELECT 
*
FROM sc 
WHERE cid IN(SELECT cid FROM teacher t JOIN course c ON t.`TId` = c.`TId` WHERE t.`Tname` = '张三') 
ORDER BY score DESC
LIMIT 1;
1
2
3
4
5
6

第四步: 关联学生表，获取学生信息，最后筛选出题目所需字段

SELECT
b.*,
a.score
FROM (
	SELECT 
	*
	FROM sc 
	WHERE cid IN(SELECT cid FROM teacher t JOIN course c ON t.`TId` = c.`TId` WHERE t.`Tname` = '张三') 
	ORDER BY score DESC
	LIMIT 1) a
JOIN student b
ON a.sid = b.sid;
1
2
3
4
5
6
7
8
9
10
11
12

如果题目还要求输出课程名称，老师名称等，则需要使用了连接查询而不是上面的子查询

SELECT 
b.*,a.score,c.cname,d.tname
FROM sc a
LEFT JOIN student b
ON a.sid = b.sid 
LEFT JOIN course c
ON a.cid = c.cid
LEFT JOIN teacher d
ON c.tid = d.tid
WHERE d.tname = '张三'
ORDER BY score DESC
LIMIT 1; 
1
2
3
4
5
6
7
8
9
10
11
12

34. 假设成绩有重复的情况下，查询选修【张三】老师所授课程的学生中，成绩最高的学生信息及其成绩

关键词：张三老师所授课程、成绩最高、学生信息
第一步：从关键词分析，可见需要用到所有的四张表
第二步：该题与第33题不同的地方就是题目的第一句，成绩可能是重复的，即最高分可能有学生相同，此时应该输出多个学生信息，那么就不能通过排序然后limit 1的方式获得最高分同学信息了，而是需要用到窗口函数dense_rank()或者rank()，另外需要注意的一点是：窗口函数是无法直接出现在where条件中的，因为从SQL的执行顺序可以看到select是在where条件之后执行的，而窗口函数出现在select中，说明在where后执行的窗口函数，因此窗口函数不能出现在where中，所以想让窗口函数作为筛选条件去过滤结果，只能再套一层子查询

SELECT 
*
FROM (
SELECT 
b.*,a.score,c.cname,d.tname,
dense_rank() over (ORDER BY score DESC) AS score_rank
FROM sc a
LEFT JOIN student b
ON a.sid = b.sid 
LEFT JOIN course c
ON a.cid = c.cid
LEFT JOIN teacher d
ON c.tid = d.tid
WHERE d.tname = '张三') f
WHERE score_rank = 1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

第一步：自连接成绩表，连接条件是学生编号相同，课程编号不同，学生成绩相同

SELECT 
* 
FROM sc a
JOIN sc b
ON a.`SId` = b.`SId` AND a.`CId` != b.`CId` AND a.`score` = b.`score`;
1
2
3
4
5

第二步：对课程去重

SELECT 
DISTINCT a.*
FROM sc a
JOIN sc b
ON a.`SId` = b.`SId` AND a.`CId` != b.`CId` AND a.`score` = b.`score`;
1
2
3
4
5

可见03号学生的01,02,03课程分数相同，都是80分