SQL经典题型解析:31-50
关联关系
--31、查询1990年出生的学生名单
(1)SELECT * from student where s_birth like '1990%';
(2)SELECT * from student where YEAR(s_birth)='1990';
2中写法结果一样
-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
分析思路：需要求每门课程的平均成绩，group by c_id
SELECT c_id, avg(s_score) avs from score GROUP BY c_id ORDER BY avs desc, c_id asc;
-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
分析思路：avg(s_score)>=85; student表
SELECT s.s_id, s_name, r.avs from student s,
(SELECT s_id, avg(s_score) avs from score GROUP BY s_id having avs>=85)r
where s.s_id=r.s_id;
--34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
分析思路：course表 c_name=数学；s_score<60；s_name和分数。
SELECT s.s_name, sc.s_score from student s,score sc where s.s_id= sc.s_id and sc.c_id = (SELECT c_id from course where c_name='数学') and s_score<60;
-- 35、查询所有学生的课程及分数情况
分析思路：可使用类似17题第二种方法的查询
SELECT s.s_id,
(SELECT s_score from score where s_id=s.s_id and c_id='01') as '语文',
(SELECT s_score from score where s_id=s.s_id and c_id='02') as '数学',
(SELECT s_score from score where s_id=s.s_id and c_id='03') as '英语',
SUM(s_score) Total from score s GROUP BY s.s_id ORDER BY total desc;
我发现这样做有点问题，好像不是所有的学生，因为有没选课的学生，那么用下面的查询语句：
SELECT s_name,r.* from student s LEFT JOIN
(SELECT s.s_id,
(SELECT s_score from score where s_id=s.s_id and c_id='01') as '语文',
(SELECT s_score from score where s_id=s.s_id and c_id='02') as '数学',
(SELECT s_score from score where s_id=s.s_id and c_id='03') as '英语',
SUM(s_score) Total
from score s GROUP BY s.s_id ORDER BY total desc)r
on s.s_id=r.s_id;
或者：
select a.s_id, a.s_name,
SUM(case c.c_name when '语文' then b.s_score else 0 end) as '语文',
SUM(case c.c_name when '数学' then b.s_score else 0 end) as '数学',
SUM(case c.c_name when '英语' then b.s_score else 0 end) as '英语',
SUM(b.s_score) as '总分'
from student a left join score b on a.s_id = b.s_id
left join course c on b.c_id = c.c_id GROUP BY a.s_id, a.s_name；
-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
分析思路：查出分数大于70 的学生编号
SELECT s.s_name, c.c_name, r.s_score from student s, course c,
(SELECT * from score where s_score>70)r
where s.s_id=r.s_id and c.c_id=r.c_id;
--37、查询不及格的学生编号，课程编号，课程名称及分数
SELECT s.s_id, s.c_id, c.c_name, s.s_score from score s RIGHT JOIN
course c on s.c_id=c.c_id and s.s_score <60;
--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
分析思路：过滤条件：c_id=01 ands_score>80；s_id和s_name 需要student和score表
SELECT s_id, s_name from student where s_id in(SELECT s_id from score where c_id='01' and s_score>80);
没有
-- 39、求每门课程的学生人数
SELECT c_id, count(s_id) from score GROUP BY c_id;
-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
分析思路：查询张三老师所交科目的学生分数，降序排列，limit1。
SELECT s.*, sc.c_id, sc.s_score from student s, score sc, course c,teacher t
where s.s_id=sc.s_id and sc.c_id=c.c_id
and c.t_id=t.t_id and t_name='张三'
ORDER BY sc.s_score desc limit 1;
或者：SELECT s.*,r.c_id, r.s_score from student s
RIGHT JOIN (SELECT * from score where c_id=
(SELECT c_id from course c, teacher t where c.t_id=t.t_id and t_name="张三")
ORDER BY s_score desc LIMIT 1)r on s.s_id=r.s_id;
--41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
分析思路：课程不同 and成绩相同
select DISTINCT b.s_id, b.c_id, b.s_score from score a, score b where a.c_id != b.c_id and a.s_score = b.s_score；
--42、查询每门功成绩最好的前两名
分析思路：和25题一样
SELECT * from score where
(select count(*) from score as a
where score.c_id = a.c_id and score.s_score<a.s_score)<2
order by c_id asc, s_score desc;
-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
分析思路：count()>5; order by count() desc, c_id
SELECT c_id, count(*) from score GROUP BY c_id having count(*)>5 ORDER BY count(*) desc, c_id;
-- 44、检索至少选修两门课程的学生学号
SELECT s_id from score GROUP BY s_id having count(c_id)>1;
--45、查询选修了全部课程的学生信息
分析思路：和11题相反。查询共有几门课，学生选修课数与总课数相等
SELECT * from student where s_id in
(SELECT s_id from score GROUP BY s_id having count(s_id)=
(SELECT count(*) from course));
--46、查询各学生的年龄
SELECT s.*, TIMESTAMPDIFF (year, s_birth, curdate()) as '年龄' from student s;
-- 47、查询本周过生日的学生
SELECT * from student where WEEKOFYEAR (s_birth)=WEEKOFYEAR(CURDATE());
没有
-- 48、查询下周过生日的学生
SELECT * from student
where WEEKOFYEAR(s_birth)=WEEKOFYEAR(CURDATE())+1;
-- 49、查询本月过生日的学生
SELECT * from student where month(s_birth)=MONTH(CURDATE());
-- 50、查询下月过生日的学生
SELECT * from student where month(s_birth)=MONTH(CURDATE())+1;
经典50道面试题分析方法及查询语句都在这3篇的内容上了，表数据是网上找的，但是建库建表，插入数据都是自己一步步做的，每一题都有自己的解题方法。希望自己的SQL编写能力有了进一步的提升，同时也希望能给各位小伙伴提供一些思路和方法，如果各位还有更好的方法还请评论区分享，大家共同学习。
