SQL五十道经典题型——（六）查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）

查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）

解题思路

1、分析题目

将题目进行分解，获取到“张三”老师的id与所讲过的课程，然后将同学的信息与张三老师的信息连接在一起。

2、获取“张三”老师的信息，通过teacher表

SELECT
	t_id,
	t_name 
FROM
	teacher 
WHERE
	t_name = "张三";

3、思考如何将“张三”老师的信息与同学的信息连接在一起需要用到几个表（重点）

学生表：
Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
课程表：
Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
教师表：
Teacher(t_id,t_name) –教师编号,教师姓名
成绩表：
Score(s_id,c_id,s_s_score) –学生编号,课程编号,分数
这个是我们之前所创建的所有的表的信息

可以知道教师表teacher与课程表course相关联，课程表course与成绩表score相关联，可以得出学生表student的s_id

3.1、通过 course 表 将 teacher 与 course 连接在一起，获取到 course 的 c_id

SELECT
	c.c_id,
	c.c_name,
	t.t_name 
FROM
	course AS c
	LEFT JOIN teacher AS t ON c.t_id = t.t_id 
WHERE
	t.t_name = "张三";

3.2、通过 score 表 将 c_id 与 s_id 连接在一起，获取到 s_id

SELECT
	sc.s_id,
	c.c_name,
	t.t_name 
FROM
	score AS sc
	LEFT JOIN course AS c ON sc.c_id = c.c_id
	LEFT JOIN teacher AS t ON c.t_id = t.t_id 
WHERE
	t.t_name = "张三";

4、获取到所有score表的s_id通过IN获取对应的学生信息

SELECT
	s.s_id,
	s.s_name 
FROM
	student AS s 
WHERE
	s.s_id IN (
	SELECT
		sc.s_id 
	FROM
		score AS sc
		LEFT JOIN course AS c ON sc.c_id = c.c_id
		LEFT JOIN teacher AS t ON c.t_id = t.t_id 
	WHERE
		t.t_name = "张三" 
	);

或者是继续采用多表联查的方式

SELECT
	s.s_id,
	s.s_name,
	t.t_name,
	c.c_name
FROM
	student AS s 
		LEFT JOIN score AS sc ON s.s_id = sc.s_id
		LEFT JOIN course AS c ON sc.c_id = c.c_id
		LEFT JOIN teacher AS t ON c.t_id = t.t_id 
	WHERE
	t.t_name = "张三";