MySQL 基本查询8--14

题目8：查询学过编号为01，并且学过编号为02课程的学生信息

-- 方法1：通过自连接实现
SELECT
	a.*
FROM
	Student a
WHERE
	a.s_id IN (
		SELECT
			b.s_id
		FROM
			Score b
		JOIN Score c ON b.s_id = c.s_id
		WHERE
			b.c_id = '01'
		AND c.c_id = '02'
	);

--方法2：通过where实现
SELECT
	s1.*
FROM
	Student s1,
	Score s2,
	Score s3
WHERE
	s1.s_id = s2.s_id
AND s1.s_id = s3.s_id
AND s2.c_id = 01
AND s3.c_id = 02;

题目9：查询学过01课程，但是没有学过02课程的学生信息

SELECT
	s1.*
FROM
	Student s1
WHERE
	s1.s_id IN (
		SELECT
			s_id
		FROM
			Score
		WHERE
			c_id = '01'
	) -- 修过01课程，要保留
AND s1.s_id NOT IN (
	SELECT
		s_id
	FROM
		Score
	WHERE
		c_id = '02'
);-- 哪些人修过02，需要排除

题目10：查询没有学完全部课程的同学的信息

解题思路：在Course表中先计算总的课程数，后对学生进行分组，筛选出每组中课程数量少于总的课程数的学生。

-- 方法1
select s.* 
from Student s  -- 学生表
left join Score s1  -- 成绩表
on s1.s_id = s.s_id
group by s.s_id  -- 学号分组
having count(s1.c_id) < (  -- 分组后学生的课程数<3
  select count(*) from Course  -- 全部课程数=3
)
-- 方法2
select s.* 
from Student s
where s_id not in (
  select s_id 
  from Score s1
  group by s_id 
  having count(*) = (select count(*) from Course)
);

题目11：查询至少有一门课与学号为01的同学所学相同的同学的信息

SELECT
	b.* 
FROM
	student b
	LEFT JOIN score c ON b.s_id = c.s_id 
WHERE
	c.c_id IN ( SELECT a.c_id FROM score a WHERE a.s_id = '01' ) 
	AND b.s_id != '01' --排除01学生自己
GROUP BY
	b.s_id;

题目12：查询和01同学学习的课程完全相同的同学的信息

方法1：因为总课程数3，而01号同学的课程数刚好是3，所以我们只要找出在Score表中课程也修满3门的同学即可。

SELECT
	b.* 
FROM
	student b
	JOIN score c ON b.s_id = c.s_id 
WHERE
	b.s_id != '01' 
GROUP BY
	b.s_id 
HAVING
	COUNT( c.c_id )=(
	SELECT
		COUNT( c_id ) 
	FROM
		score 
WHERE
	s_id = '01')

方法2：使用group_concat函数（分组合并，同时排序） 1）group_concat的使用方法为：

group_concat([DISTINCT] 字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
SELECT
	b.s_id,
	b.s_name,
	b.s_sex,
	GROUP_CONCAT( c.c_id ORDER BY c.c_id ) AS concat_course 
FROM
	student b
	JOIN score c ON b.s_id = c.s_id 
GROUP BY
	b.s_id 
HAVING
	GROUP_CONCAT( c.c_id ORDER BY c.c_id )=(
	SELECT
		GROUP_CONCAT( c_id ORDER BY c_id ) 
	FROM
		score 
	WHERE
		s_id = '01' 
	);

题目13：查询没有修过张三老师讲授的任何一门课程的学生姓名

-- 方法1
SELECT
	*
FROM
	student
WHERE
	s_id NOT IN (
		SELECT DISTINCT
			t1.s_id
		FROM
			score t1
		JOIN course t2 ON t1.c_id = t2.c_id
		WHERE
			t2.t_id = (
				SELECT
					t_id
				FROM
					teacher
				WHERE
					t_name = '张三'
			)
	) 

-- 方法2
SELECT
		* -- 4、学号取反找到学生信息
	FROM
		Student
	WHERE
		s_id NOT IN (
			SELECT DISTINCT
				(s_id) -- 3、课程号找到对应的学号
			FROM
				Score
			WHERE
				c_id = (
					SELECT
						c_id -- 2、教师编号找到对应的课程号
					FROM
						Course
					WHERE
						t_id = (
							SELECT
								t_id -- 1、姓名找到教师编号
							FROM
								Teacher
							WHERE
								t_name = '张三'
						)
				)
		);

题目14：查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT
	s.s_id,
	s.s_name,
	t.avg_score
FROM
	Student s
JOIN (
	SELECT
		s_id,
		ROUND(AVG(s_score)) avg_score  --求均值再取整
	FROM
		Score
	WHERE
		s_score < 60
	GROUP BY
		s_id
	HAVING
		COUNT(s_score) >= 2
) t ON s.s_id = t.s_id