【SQL】进阶57题
1. 题号1142 查询姓“李”的教师数量
select count(t_id) from teacher where t_name like '李%';
/*
1. 大水题，无脑写
2. 题号1143 查询课程编号为”01“的课程比”02“的课程成绩高的所有学生的学号
select sc1.s_id , sc1.s_score , sc2.s_score from student
left join
(select s_id , s_score from score where score.c_id = '01') sc1 on student.s_id = sc1.s_id
left join
(select s_id , s_score from score where score.c_id = '02') sc2 on sc1.s_id = sc2.s_id
where sc1.s_score > sc2.s_score;
/*
select sc1.s_id
from
(select s_id , s_score from score where score.c_id = '01') sc1
join
(select s_id , s_score from score where score.c_id = '02') sc2
on sc1.s_id = sc2.s_id
where sc1.s_score > sc2.s_score
1. 注释的这部分是题解，写到我怀疑人生，结果是老师出的题目有问题，必须输出学号，姓名，01成绩，02成绩才能AC
2. 使用子查询从成绩表中获取课程编号为'01'和'02'的学生编号和分数，然后将这两个子查询结果进行联结，
并筛选出成绩比课程'02'高的学生的学号。
3. 题号1144 查询平均成绩大于60分的学生的学号和平均成绩
select s_id , avg(s_score) from score
group by s_id
having avg(s_score) > 60;
/*
1. 从score表中选择学生编号（s_id）和成绩的平均值（AVG(s_score)）作为结果列。
2. 使用GROUP BY子句将数据按照学生编号（s_id）进行分组。
3. 对每个学生分组计算其成绩的平均值。
4. 使用HAVING子句筛选出平均成绩大于60的学生。
4. 题号1145 查询所有学生的学号、姓名、选课数、总成绩
select student.s_id , s_name, ifnull(count(score.c_id) , 0) , ifnull(sum(score.s_score) , 0) from student
left join score on student.s_id = score.s_id
group by student.s_id;
/*
1. 通过左连接（LEFT JOIN），将student表与score表连接在一起，以便获取每个学生的成绩信息。
2. 使用COUNT函数计算每个学生所拥有的成绩数量，并使用SUM函数计算每个学生的总成绩。
3. 如果没有匹配的成绩记录，这两个聚合函数的结果将为NULL。
4. 使用IFNULL函数将NULL结果替换为0，以确保结果中显示为0而不是NULL。
5. 题号1146 查询没学过“张三”老师课的学生的学号、姓名
select s_id , s_name from student
where s_id not in (
select student.s_id from student
left join score on student.s_id = score.s_id
where c_id = (
select c_id from course where t_id = (select t_id from teacher where t_name = '张三')
)
);
/*
1. 从student表中选择学生编号（s_id）和学生姓名（s_name）作为结果列。
2. 使用子查询来获取所有学过“张三”老师课程的学生的学生编号。
子查询中的第一个子查询是从teacher表中选择教师编号（t_id），以获得“张三”老师的教师编号。
第二个子查询是从course表中选择课程编号（c_id），以获取与“张三”老师相关的课程编号。
最后一个子查询是从score表中选择学生编号（s_id），以获取学过与“张三”老师课程相关的学生编号。
3. 主查询中使用NOT IN子句来选择在学生表（student）中学号不在子查询结果中的学生的学号和姓名。
6. 题号1147 查询学过“张三”老师所教的所有课的同学的学号、姓名
select student.s_id , student.s_name from student
left join score on student.s_id = score.s_id
where c_id = (
select c_id from course where t_id = (select t_id from teacher where t_name = '张三')
);
/*
1. 这题就是上一题的核心部分，详见上一题思路。
7. 题号1148 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名
select student.s_id , s_name from student
left join (select * from score where c_id = '01') sc1 on student.s_id = sc1.s_id
left join (select * from score where c_id = '02') sc2 on sc1.s_id = sc2.s_id
where sc1.s_id = sc2.s_id;
/*
1. 从student表中选择学生编号（s_id）和学生姓名（s_name）作为结果列。
2. 使用左连接（LEFT JOIN）将student表与两个子查询连接起来。
第一个子查询选择了score表中课程编号为'01'的所有记录（即学生参加的'01'课程成绩）。
第二个子查询选择了score表中课程编号为'02'的所有记录（即学生参加的'02'课程成绩）。
3. 在连接过程中，通过student.s_id = sc1.s_id将学生表与第一个子查询的结果连接起来，
再通过sc1.s_id = sc2.s_id将第一个子查询的结果与第二个子查询的结果连接起来。
这样，将具有相同学生编号的记录连接在一起。
4. 最后，通过WHERE子句过滤出满足条件sc1.s_id = sc2.s_id的记录，即同时参加了'01'课程和'02'课程的学生。
5. 返回满足条件的学生的学生编号（s_id）和学生姓名（s_name）作为查询结果。
8. 题号1149 查询课程编号为“02”的总成绩
select c_id , sum(s_score) from score where c_id = '02';
/*
大水题
9. 题号1150 查询所有课程成绩小于60分的学生的学号、姓名
select distinct student.s_id , s_name from student
left join score on student.s_id = score.s_id
where s_score < 60 or s_score is null;
/*
1. 注意，我写了半天发现漏看题目细节了，需要考虑学生缺考没有成绩的情况，还要去重。
2. 从student表中选择学生编号（s_id）和学生姓名（s_name）作为结果列。
使用左连接（LEFT JOIN）将student表与score表连接起来。通过student.s_id = score.s_id将两个表关联起来。
3. 在连接后的结果上应用过滤条件。过滤条件是s_score < 60或s_score为空值的记录。
4. 使用DISTINCT关键字确保返回的结果中没有重复的学生记录。
5. 返回满足条件的学生的学生编号（s_id）和学生姓名（s_name）作为查询结果。
10. 查询没有学全所有课的学生的学号、姓名
select student.s_id , s_name from student
where student.s_id not in (
select s_id from score
group by s_id
having count(c_id) >= (select count(c_id) from course)
);
/*
select student.s_id , s_name from student
where student.s_id in (
select s_id from score
group by s_id
having ifnull(count(c_id) , 0) < (select count(c_id) from course)
)
1. 见鬼了，我只是把我的条件反向描述，就过了。
2. 使用NOT IN运算符来排除满足子查询条件的学生。
3. 子查询通过分组计数（COUNT）来统计每个学生学习的课程数量，然后与总课程数量进行比较。
4. 如果学生学习的课程数量大于或等于总课程数量，那么该学生被视为学习了所有课程。
5. 主查询中的NOT IN运算符将排除满足该条件的学生，返回剩余的学生。
11. 题号1152 查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名
select distinct student.s_id , s_name from student
left join score on student.s_id = score.s_id
where c_id in (select c_id from score where s_id = '01') and student.s_id != '01';
/*
1. 从student表中选择学生编号（s_id）和学生姓名（s_name）作为结果列。
2. 使用左连接（LEFT JOIN）将student表与score表连接起来，以学生编号为条件进行关联（student.s_id = score.s_id）。
3. 在连接后的结果上应用过滤条件。过滤条件是学生的课程编号（c_id）在与学号为'01'的学生相同的课程编号中，
同时排除学号为'01'的学生本身（student.s_id != '01'）。
4. 使用DISTINCT关键字确保返回的结果中没有重复的学生记录。
5. 返回满足条件的学生的学号（s_id）和姓名（s_name）作为查询结果。
12. 题号1153 查询和“01”号同学所学课程完全相同的其他同学的学号
select s_id , s_name from student
where s_id != '01' and s_id in (
select s_id from score
group by s_id
having count(c_id) = (select count(c_id) from score where s_id = '01')
);
/*
1. 好吧，我在起初的时候一直在想如何理解“所学课程完全相同”这句话，到底是每个课程号和学习的课程数量相等还是
只要学习的课程数量一致即可，想了好一会在脑海里没有接触过能够让课程号和学习课程数量都相等的实现方法，最
后决定先试试课程数量相等，然后就一次过了，注意老师题目有问题，这里必须输出学号和姓名才能AC。
2. 从student表中选择学生编号（s_id）和学生姓名（s_name）作为结果列。
3. 在外部查询中使用条件 s_id != '01'，排除学号为'01'的学生本身。
4. 使用子查询来确定学号为'01'的学生所学课程的数目。
内部子查询 SELECT count(c_id) FROM score WHERE s_id = '01' 返回学号为'01'的学生所学课程的数目。
在外部查询中，使用子查询作为过滤条件，
即 s_id IN (SELECT s_id FROM score GROUP BY s_id HAVING count(c_id) =
(SELECT count(c_id) FROM score WHERE s_id = '01'))。
内部子查询 SELECT s_id FROM score GROUP BY s_id HAVING count(c_id) =
(SELECT count(c_id) FROM score WHERE s_id = '01')
返回与学号为'01'的学生所学课程数目相同的其他学生的学号。
5. 返回满足条件的学生的学号（s_id）和姓名（s_name）作为查询结果。
13. 题号1154 查询没学过"张三"老师讲授的任一门课程的学生姓名
select distinct s_id , s_name from student
where s_id not in (
select distinct s_id from score where c_id in (
select c_id from course where t_id = (
select t_id from teacher where t_name = '张三'
)
)
);
/*
1. 这里仍然需要输出学号和姓名才能AC。
2. 外部查询使用 SELECT DISTINCT s_id, s_name 来选择学生的学号和姓名。
3. 在外部查询的 WHERE 子句中，使用 NOT IN 关键字来排除学过"张三"老师讲授的任一门课程的学生。
4. 子查询中的第一个内部查询 SELECT c_id FROM course WHERE t_id =
(SELECT t_id FROM teacher WHERE t_name = '张三') 用于获取"张三"老师所讲授的课程编号。
5. 子查询中的第二个内部查询 SELECT DISTINCT s_id FROM score WHERE c_id IN (...)
用于获取学过上述课程编号的学生学号。
6. 外部查询中的 NOT IN 条件将过滤出没有出现在上述学生学号列表中的学生。
14. 题号1155 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select student.s_id , s_name , avg(s_score) from student
left join score on student.s_id = score.s_id
where s_score < 60
group by s_id
having count(score.s_score) > 1;
/*
1. 这个题目有坑，不及格课程不包括那些没有分数的，就是null的值不会被算进去。
2. SELECT student.s_id, s_name, AVG(s_score): 这部分指定了查询结果的列，包括学生的学号、姓名和平均成绩。
3. FROM student LEFT JOIN score ON student.s_id = score.s_id: 这是一个左连接操作，将学生表和成绩表连接起来。
通过学生表的学生编号 (s_id) 和成绩表的学生编号 (score.s_id) 进行连接。
4. WHERE s_score < 60: 这是一个过滤条件，只选择成绩低于 60 分的记录。
5. GROUP BY s_id: 这是一个分组操作，按照学生的学号进行分组。
6. HAVING COUNT(score.s_score) > 1: 这是一个筛选条件，在分组后的结果中，只选择至少有两个不及格成绩记录的学生。
15. 题号1156 检索"01"课程分数小于60，按分数降序排列的学生信息
select distinct student.s_id , s_name , s_birth , s_sex from student
left join score on student.s_id = score.s_id
where student.s_id in (
select s_id from score
where c_id = '01' and s_score < 60
)
order by s_score desc;
/*
1. SELECT DISTINCT student.s_id, s_name, s_birth, s_sex:
这部分指定了查询结果的列，包括学生的学号、姓名、生日和性别。
2. FROM student LEFT JOIN score ON student.s_id = score.s_id:
这是一个左连接操作，将学生表和成绩表连接起来。通过学生表的学生编号 (s_id)
和成绩表的学生编号 (score.s_id) 进行连接。
3. WHERE student.s_id IN (SELECT s_id FROM score WHERE c_id = '01' AND s_score < 60):
这是一个过滤条件，只选择在成绩表中选修课程编号为 '01' 且成绩低于 60 分的学生的记录。
4. ORDER BY s_score DESC: 这是一个排序操作，按照成绩降序排序，即将成绩高的学生排在前面。
更多敬请期待
