SQL面试必会50题

引用：

视频讲解：https://www.bilibili.com/video/BV1q4411G7Lw/

SQL面试必会50题： https://zhuanlan.zhihu.com/p/43289968

图解SQL面试题：经典50题：https://zhuanlan.zhihu.com/p/38354000

其中重点为：1/2/5/6/7/10/11/12/13/15/17/18/19/22/23/25/31/35/36/40/41/42/45/46 共16题

超级重点 18和23、 22和25 、 41、46

---

几个重要函数

①按照某列排序：row_number(）over （[partition by 字段(按照字段分组)] order by 列）【19】

②行转列、按值分段：CASE WHEN c_id = ‘01’ THEN s_score ELSE null END【17、35】

③日期函数如年月日：year()、week()、month()【31】

④查询年龄、查询生日：datediff(data1, data2)，返回data1-data2的天数【46，47，48，50】

图解SQL面试题：经典50题

ps：这些题考察SQL的编写能力，对于这类型的题目，需要你先把4张表之间的关联关系搞清楚了，最好的办法是自己在草稿纸上画出关联图，然后再编写对应的SQL语句就比较容易了。下图是我画的这4张表的关系图，可以看出它们之间是通过哪些外键关联起来的。

已知有如下4张表：

学生表：
Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别

成绩表：
Score(s_id,c_id,s_score) –学生编号,课程编号,分数

课程表：
Course(c_id,c_name,t_id) –课程编号, 课程名称, 教师编号

教师表：
Teacher(t_id,t_name) –教师编号,教师姓名

测试数据：

-- 建表
-- 学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
-- 课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
-- 教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
-- 成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
-- 插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
-- 课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

-- 教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

-- 成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

1. 查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）

-- 难点在于表的连接
-- 1. 查询课程编号为“01”的课程：先把课程为01的学生找出来
-- 2. “02”的课程：再把课程为02的学生找出来
-- 3. 01 比 02 成绩高 ：将两张表join起来做连接查询
select a.s_id from
(
select s_id, c_id, s_score from score where c_id = '01'
) as a
inner join
(
select s_id, c_id, s_score from score where c_id = '02'
) as b
on a.s_id = b.s_id
where a.s_score > b.s_score

2. 查询平均成绩大于60分的学生的学号和平均成绩

select s_id, avg(s_score)as avg_score
from score
GROUP BY s_id
having avg(s_score) > 60

-- 如果算上三门成绩的平均分>60分
select s_id, avg_score 
from (
select s_id, sum(s_score)/3 as avg_score
from score
GROUP BY s_id
) a
where avg_score > 60

3. 查询所有学生的学号、姓名、选课数、总成绩

select a.s_id, a.s_name, count(b.c_id), sum(b.s_score)
from student a, score b
where a.s_id = b.s_id
group by a.s_id

select a.s_id, a.s_name, count(b.c_id), sum(b.s_score)
from student a
inner join score b
on a.s_id = b.s_id
group by a.s_id

4. 查询姓“猴”的老师的个数

-- _任意匹配一个字符，% 任意匹配多个字符
-- =匹配一个准确值，like匹配一个范围
select count(t_id) 
from teacher
where t_name like '猴%'

5. 查询没学过“张三”老师课的学生的学号、姓名（重点）

注：子查询

-- 1. 查询张三老师的课
-- 2. 找出没上过老师课的学生
select * 
from student
where s_id not in (
	select s.s_id from score s
	inner join course c on c.c_id = s.c_id
	inner join teacher t on t.t_id = c.t_id
	where t.t_name = "张三"
)
-- 使用join的写法
select s1.*
from student as s1
left join  (
	select s.s_id from score s
	inner join course c on c.c_id = s.c_id
	inner join teacher t on t.t_id = c.t_id
	where t.t_name = "张三"
) as s2
on s1.s_id =s2.s_id
where s2.s_id is null

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

注：子查询

-- 1. 查询张三老师的课
-- 2. 找出上过老师课的学生
select * 
from student
where s_id in (
	select s.s_id from score s
	inner join course c on c.c_id = s.c_id
	inner join teacher t on t.t_id = c.t_id
	where t.t_name = "张三"
)

7. 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）

-- 思路：把01、02课程的学号分别子查询出来 ，
-- 然后inner join 或者 INTERSECT 将表的公共区域进行合并
select * from student
where s_id in (
	select a.s_id from (
			select s_id from score where c_id = '01'
		) as a
	inner join(
			select s_id from score where c_id = '02'
	) as b
	on a.s_id = b.s_id
)
-- 效率优化(不要使用in关键字，会导致索引失效，耗时长)
-- from score inner join score
select * from student s 
inner join (
	select a.s_id from (
			select s_id from score where c_id = '01'
		) as a
	inner join(
			select s_id from score where c_id = '02'
	) as b
	on a.s_id = b.s_id
) b
where s.s_id = b.s_id

---

相似题目：查询学过编号为“01”的课程并且但没有学过编号为“02”的课程的学生

-- 1. 先用student表左连接学过课程'01'的和学过课程'02'
select s.*, sc1.s_score, sc2.s_score from student s
left join (select * from score where c_id = '01') sc1 on s.s_id = sc1.s_id
left join (select * from score where c_id = '02') sc2 on s.s_id = sc2.s_id
-- 2. 筛选学过课程'01'但没有学过课程'02'的学生
where sc1.c_id = '01' and sc2.c_id is null;

8. 查询课程编号为“02”的总成绩

select c_id, sum(s_score)
from score
where c_id = '02'
-- 最好用groupby + having
-- 		（子查询的效率一般低于关联查询，所以尽量用表联接来代替不必要的子查询。）
-- 求和，求平均，求数量
select c_id, sum(s_score), avg(s_score), count(s_score)
from score
GROUP BY c_id 
HAVING c_di = '02'

9. 查询所有课程成绩小于60分的学生的学号、姓名

-- 1. 查询最大的课程成绩小于60分的学生id
-- 2. 进行表连接
select s.s_id, s_name 
from student s 
inner join (
	select s_id 
	from score
	GROUP BY s_id
	having max(s_score) < 60
) as b
on s.s_id = b.s_id 

10. 查询没有学全所有课的学生的学号、姓名(重点)

-- 1. 分组统计，成绩数量小于课程的数量就是没学全（错误答案）
select s.s_id, s.s_name from student s
INNER JOIN (
	select s_id from score
	group by s_id
	having count(distinct c_id) < (
			select count(distinct c_id) from course
			)
) as b
on s.s_id = b.s_id

-- 上面的做法有错误：如果有个人一门也没有选，则不会出现在score表中
-- 正确做法：使用left join将学生表和成绩表合并起来
select st.s_id, st.s_name
from student as st
left join score as sc on st.s_id = sc.s_id 
group by st.s_id
having count(distinct sc.c_id) < (
		select count(distinct c_id) from course
)

11. 查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名（重点）

-- 1. 查学号01所学的课程
-- 2. 查询IN 上面所学课程的学号
-- 3. 和学生信息表并表
select st.s_id, st.s_name from student as st
INNER JOIN (
		select distinct sc1.s_id from score as sc1
		inner join 
			(select c_id from score where s_id = '01') as sc2 	
		on sc1.c_id = sc2.c_id
		where s_id != '01') as s 
on st.s_id = s.s_id

12. 查询和“01”号同学所学课程完全相同的其他同学的学号(重点)

-- 1. 找出修了与01号同学不同课的同学
-- 2. 找出修课数量与01同学相同的同学id
-- 3. 去除掉条件1中的同学
-- 4. 查出同学的信息
select * from student 
where s_id in (
select s_id from score
where s_id != '01'
group by s_id 
having count(distinct c_id) = 
				(select count(distinct c_id) from score where s_id = '01')
) and s_id not in(
select distinct s_id from score
where c_id not in ( select c_id from score where s_id = '01')
)

13. 查询没学过"张三"老师讲授的任一门课程的学生姓名（重点，同5）

select s1.*
from student as s1
left join  (
	select s.s_id from score s
	inner join course c on c.c_id = s.c_id
	inner join teacher t on t.t_id = c.t_id
	where t.t_name = "张三"
) as s2
on s1.s_id =s2.s_id
where s2.s_id is null

15. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点）

-- 1. 统计学生不及格的成绩数量 >= 2 和成绩
-- 2. 与学生表并表
select st.* , s.avg_score from student as st
inner join (
	select s_id, count(c_id), avg(s_score) as avg_score from score
	where s_score < 60
	GROUP BY s_id
	having count(distinct c_id) >= 2
) as s
on st.s_id = s.s_id

16. 检索"01"课程分数小于60，按分数降序排列的学生信息（与3.4题重复）

select st.*, s.c_id, s.s_score from student st
inner JOIN score as s
on st.s_id = s.s_id
where s.c_id = '01' and s.s_score < 60
order by s_score desc

-- 拓展：要同时查询出没考01的学生呢？  左表连接
select st.*, s.c_id, s.s_score from student st
left JOIN score as s
on st.s_id = s.s_id
where (s.c_id = '01' or s.c_id is null) and (s.s_score < 60 or s.s_score is null)
order by s_score desc

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重重点与35一样)

备注：（对分别求和时往往会用到case when）

1.因为要选出需要的字段 用case when 当c_id=‘01’ then 可以得到对应的 s_core

2.因为GROUP UP 要与select 列一致，所以case when 加修饰max

3.因为最后要展现出每个同学的各科成绩为一行，所以用到case

-- 普通写法 学号 课程号 课程得分 平均分（不满足业务需求）
select s1.s_id, s1.c_id, s1.s_score, s2.avg_score from score as s1
inner join (
select s_id, avg(s_score) as avg_score from score
group by s_id 
)as s2
on s1.s_id = s2.s_id
ORDER BY s2.avg_score desc
-- 期望的写法 学号 课程1 课程2 课程3 平均分（如何行转列？）
select s_id "学号"
, MAX(CASE WHEN c_id = '01' THEN s_score ELSE null END) "语文"
, MAX(CASE WHEN c_id = '02' THEN s_score ELSE null END) "数学"
, MAX(CASE WHEN c_id = '03' THEN s_score ELSE null END) "英语"
, avg(s_score) "平均成绩"
from score
GROUP BY s_id 
order by avg(s_score) desc

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 (超级重点)

–及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 (超级重点)

-- 及格率： count(>=60) / count(*)
select s.c_id
, c.c_name
, max(s.s_score) "最高分"
, min(s.s_score) "最低分"
, avg(s.s_score) "平均分"
, sum(case when s.s_score >= 60 then 1 else 0 end) / count(s_id) "及格率"
, sum(case when s.s_score >= 70 and s.s_score < 80 then 1 else 0 end) / count(s_id) "中等率"
, sum(case when s.s_score >= 80 and s.s_score < 90 then 1 else 0 end) / count(s_id) "优良率"
, sum(case when s.s_score >= 90 then 1 else 0 end) / count(s_id) "优秀率率"
from score as s
inner join course as c on s.c_id = c.c_id
group by c_id

19. 按各科成绩进行排序，并显示排名(重点)

重点：row_number(）over （[partition by 字段(按照字段分组)]order by 列）

mysql8.0窗口函数：https://developer.aliyun.com/article/593698

row_number 排序值从小到大,依次排列 1234

dense_rank 相同数据,并列保存,不存在断值(一直连续) 1223

rank 相同数据并列保存,下一个值跳值(断续) 1224

SELECT *
, ROW_NUMBER() OVER(PARTITION BY c_id order by s_score desc) as 'rank'
from score

20. 查询学生的总成绩并进行排名

select s_id, sum(s_score)
from score
GROUP BY s_id
order by sum(s_score) desc

21. 查询不同老师所教不同课程平均分从高到低显示

select b.t_id, b.t_name, s.c_id, b.c_name, avg(s.s_score) from score as s 
inner join (
select distinct sc.c_id, c.c_name, t.t_id, t.t_name from score as sc
inner join course as c on c.c_id = sc.c_id
inner join teacher as t on t.t_id = c.t_id
) as b on s.c_id = b.c_id
GROUP BY s.c_id
ORDER BY avg(s.s_score) desc

22. 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（重要 25类似）

总结：

1.row_number () over (partition by 分组列 order by 排序列)

2.先分组后排序，作为一个表

3.从表中筛选出某行，用where

备注：如果想选出学生信息，那成绩，就是以列的形式出现，如果1.2.3名以行的形式出现，就不能有对应的学生信息。（见25题

-- 1. 按照课程分组进行排序（同19题）
-- 2. 找出排名第二第三的
select *
from (
	select st.s_id, st.s_name, st.s_birth, st.s_sex, c_id, s_score
	, ROW_NUMBER() OVER(PARTITION BY c_id ORDER BY s_score DESC) m
	from score sc 
	INNER JOIN student st on sc.s_id = st.s_id
	) a
where m in (2,3)

23. 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称(重点和18题类似)

select sc.c_id, c.c_name
, sum(case when sc.s_score < 60 then 1 else 0 end) "x<60"
, sum(case when sc.s_score >= 60 and sc.s_score < 70 then 1 else 0 end) "60<x<70"
, sum(case when sc.s_score >= 70 and sc.s_score < 85 then 1 else 0 end) "70<x<85"
, sum(case when sc.s_score >= 85 and sc.s_score < 100 then 1 else 0 end) "85<x<100"
from score as sc inner join course as c on c.c_id = sc.c_id
GROUP BY c_id

24. 查询学生平均成绩及其名次（同19题，重点）

一起排名就不用partition，因为partition是组内在排名，前面的案例都是语数外各一组分别排

select s_id, avg(s_score)
, ROW_NUMBER() OVER(order by avg(s_score) desc) as 'rank'
from score
GROUP BY s_id

25. 查询各科成绩前三名的记录（不考虑成绩并列情况）（重点 与22题类似）

-- 错误答案
select c_id, s_score
, (CASE WHEN m = 1 then s_score else null end) "第一"
, (CASE WHEN m = 2 then s_score else null end) "第二"
, (CASE WHEN m = 3 then s_score else null end) "第三"
from (
	select st.s_id, st.s_name, st.s_birth, st.s_sex, c_id, s_score
	, ROW_NUMBER() OVER(PARTITION BY c_id ORDER BY s_score DESC) m
	from score sc 
	INNER JOIN student st on sc.s_id = st.s_id
	) a
where m in (1, 2,3)

-- 正确答案（把三行变成三列，取这行的最大值max）
-- 1. 按照课程分组进行排序（同19题）
-- 2. 找出排名第二第三的
select c_id
, max(CASE WHEN m = 1 then s_score else null end) "第一"
, max(CASE WHEN m = 2 then s_score else null end) "第二"
, max(CASE WHEN m = 3 then s_score else null end) "第三"
from (
	select st.s_id, st.s_name, st.s_birth, st.s_sex, c_id, s_score
	, ROW_NUMBER() OVER(PARTITION BY c_id ORDER BY s_score DESC) m
	from score sc 
	INNER JOIN student st on sc.s_id = st.s_id
	) a
where m in (1, 2,3)
group by c_id

26. 查询每门课程被选修的学生数

用course表left join score课程计数结果表，这样才不会漏掉没人选的课程

select c.c_id, c.c_name, count(s_score)
from course c
left join score s on c.c_id = s.c_id
group by c.c_id

27. 查询出只有两门课程的全部学生的学号和姓名

select st.*
from student as st
inner join (
	select s_id, count(s_score)
	from score
	group by s_id
	HAVING count(s_score) = 2
	) as b on st.s_id = b.s_id

28. 查询男生、女生人数

select s_sex, count(s_sex)
from student
GROUP BY s_sex

-- 行转列的写法
select 
sum(case when s_sex = '男' then 1 else 0 end) "男生"
, sum(case when s_sex = '女' then 1 else 0 end) "女生"
from student

29. 查询名字中含有"风"字的学生信息

select *
from student
where s_name like '%风%'

31. 查询1990年出生的学生名单（重点year）

select *
from student
where year(s_birth) = 1990
-- 或者
select *
from student
where s_birth like '1990%'

32. 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

select st.s_id, st.s_name, avg(s.s_score) from student st
inner join score s on s.s_id = st.s_id
group by st.s_id
having avg(s.s_score) > 85

33. 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

select c_id, avg(s_score) 
from score
group by c_id
ORDER BY avg(s_score) asc, c_id desc

34. 查询课程名称为"数学"，且分数低于60的学生姓名和分数

select s.s_name, sc.s_id, sc.c_id, c.c_name, avg(sc.s_score) from score sc 
inner join student s on s.s_id = sc.s_id
inner join course c on c.c_id = sc.c_id
where c.c_name = '数学'
group by sc.s_id, sc.c_id having avg(sc.s_score) < 60

35. 查询所有学生的课程及分数情况（重点）

1.因为要选出需要的字段 用case when 当co.c_id=‘id’ then 可以得到对应的 sc.s_core

2.因为GROUP UP 要与select 列一致，所以case when 加修饰max

3.因为最后要展现出每个同学的各科成绩为一行，所以用到case

select st.s_id, st.s_name
, MAX(CASE WHEN s.c_id = '01' THEN s.s_score ELSE NULL end) "语文"
, MAX(CASE WHEN s.c_id = '02' THEN s.s_score ELSE NULL end) "数学"
, MAX(CASE WHEN s.c_id = '03' THEN s.s_score ELSE NULL end) "英语"
, avg(s.s_score)
from student st
left join score s on st.s_id = s.s_id
group by s_id
ORDER BY avg(s.s_score) desc

36. 查询任何一门课程成绩在70分以上的姓名、课程名称和分数（重点）

注：不用group by

select st.s_id, st.s_name, s.c_id, s.s_score
from student st
left join score s on st.s_id = s.s_id
where s_score > 70

37. 查询不及格的课程并按课程号从大到小排列

select st.s_id, st.s_name, s.c_id, c.c_name, s.s_score
from student st
left join score s on st.s_id = s.s_id
left join course c on c.c_id = s.c_id
where s_score < 60
ORDER BY s.c_id

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

select st.s_id, st.s_name, s.c_id, s.s_score
from student st
left join score s on st.s_id = s.s_id
where s_score > 80 and s.c_id = '03'

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

select c.c_name, sc.c_id, count(sc.s_score) from course c
left join score sc on sc.c_id = c.c_id
GROUP BY c.c_id

40. 查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩（重要）

SQL SERVER 中用top

MYSQL 用 limit

-- 查询张三老师教授的课程
-- 用ROW_NUMBER()排序
-- 用limit找到第一个
select st.s_name, sc.s_score
,ROW_NUMBER() OVER(ORDER BY sc.s_score desc) as 'rank'
from student st 
inner join score sc on sc.s_id = st.s_id
inner join (
select distinct sc.c_id from score sc
left join course c on c.c_id = sc.c_id
left join teacher t on c.t_id = t.t_id
where t.t_name = '张三'
) as b on b.c_id = sc.c_id
limit 0,1
-- 上面写复杂了，可以直接用inner join并表查询
select s.s_id, st.s_name,c.c_id,s.s_score
from score as s
inner join course as c on s.c_id = c.c_id
inner join teacher as t on c.t_id =t.t_id
inner join student as st on s.s_id = st.s_id
where t.t_name = '张三'
ORDER BY s.s_score desc 
limit 0, 1

41. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 （重点）

-- 查询一个学生在不同课程上成绩相同
select distinct a.s_id, a.c_id, a.s_score from score a
INNER JOIN score b on a.s_id = b.s_id
where a.s_score = b.s_score and a.c_id != b.c_id

-- 查询几个课程中 成绩相同的学生？

42. 查询每门功成绩最好的前两名（同22和25题）

select * from (
		select s_id, c_id, s_score
, ROW_NUMBER() OVER(PARTITION by c_id order by s_score) 'rank'
from score
) as a
where a.rank in (1, 2)

43. 统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select c_id, count(s_score) 
from score
GROUP BY c_id
HAVING count(s_score) > 5
order by count(s_score) desc, c_id asc

44. 检索至少选修两门课程的学生学号

select s_id, count(c_id) from score
group by s_id
having count(c_id) >= 2

45、 查询选修了全部课程的学生信息

select s_id, count(c_id)
from score 
group by s_id 
having count(c_id) = (
	select count(*) from course
)

47、查询没学过“张三”老师讲授的任一门课程的学生姓名

select st.* from student as st
where st.s_id not in (
	select sc.s_id from score as sc
inner join course as c on c.c_id = sc.c_id
inner join teacher as t on t.t_id = c.t_id
where t.t_name = '张三'
)
-- 不使用not in的做法
select st.*, a.s_id from student as st
left join (
	select sc.s_id from score as sc
	inner join course as c on c.c_id = sc.c_id
		inner join teacher as t on t.t_id = c.t_id
	where t.t_name = '张三'
) as a on a.s_id = st.s_id
where a.s_id is null

48、查询两门以上不及格课程的同学的学号及其平均成绩

select s_id, avg(s_score) from score
where s_score < 60
group by s_id
having count(s_score) < 60

46. 查询各学生的年龄（精确到月份）

备注：年份转换成月份，比如结果是1.9，datediff 最后取1年

-- 用datadiff
select s_id, s_birth
, datediff('2022-6-19', s_birth)/365 
from student
-- NOW()获取现在的时间
select s_id, s_birth
, datediff(Now(), s_birth)/365 
from student

49. 查询本周过生日的学生

select * from student
where week(concat('2022-', substring(s_birth, 6,5)), 1) = WEEK(NOW(), 1)
-- s_birth = '2019-12-27' , 
-- substring(s_birth, 6,5) ==> 取出月和日
-- concat('2020-', substring(s_birth, 6,5)) ==> 将今年和月日进行拼接

50. 查询下周过生日的学生

select * from student
where week(concat('2022-', substring(s_birth, 6,5)), 1) = WEEK(NOW(), 1)+1

51. 查询本月过生日的学生

select * from student
where MONTH(s_birth) = MONTH(NOW())

52. 查询下个月过生日的学生

select * from student
where MONTH(s_birth) = MONTH(NOW())+1

其他

1. 查询班级前10名的分数,包括并列的分数

#2、再进行范围score的查询
select * from student t1 WHERE score >=(
	#1、锁定最后一个的分数值
	select distinct score 
	FROM student t2 
	order by t2.score DESC 
	LIMIT 9,1
) order by t1.score DESC;