MySql查询本周或下周过生日的人

这几题我想了很久,因此记录一下.
做题思路,先将学生的生日年份统一成当前时间的年份,这样就可以直接用相差的天数判断是否本周或者下周过生日了
首先还是来建表加数据吧

-- 创建表格
create table student(
	s_id varchar(20) not null,
	s_name varchar(255) not null default '',
	s_birth varchar(20) not null default '',
	s_sex varchar(10) not null default ''
)

-- 插入数据
INSERT INTO student VALUES 
('01','赵雷','1990-01-01','男'),
('02','钱电','1990-12-21','男'),
('03','孙风','1990-05-20','男'),
('04','李云','1990-08-06','男'),
('05','周梅','1991-12-01','女'),
('06','吴兰','1992-03-01','女'),
('07','郑竹','1989-07-01','女'),
('08','王菊','1990-01-20','女'),
('09','张飞','1990-9-25','男'),
('10','刘备','1990-01-25','男'),
('11','关羽','1990-01-25','男'),
('12','金毛狮王','1850-05-24','男'),
('13','黄蓉','1890-05-31','女'),
('14','郭靖','1990-06-06','男'),
('15','潇潇','1995-06-07','女');

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

select A.s_id,A.s_name from 
(
select s_id,s_name,DATEDIFF(CURDATE(),ADDDATE(s_birth,INTERVAL year(curdate()) - year(s_birth) year)) diff from student
) A
where A.diff < DAYOFWEEK(curdate()) and A.diff >= DAYOFWEEK(curdate()) - 7;

-- 结果展示
+------+----------+
| s_id | s_name   |
+------+----------+
| 12   | 金毛狮王 |
+------+----------+

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

select A.s_id,A.s_name from 
(
select s_id,s_name,DATEDIFF(ADDDATE(s_birth,INTERVAL year(curdate()) - year(s_birth) year),CURDATE()) diff from student
) A
where A.diff > (7 - DAYOFWEEK(curdate()))  and A.diff <= 14 - DAYOFWEEK(curdate());

-- 结果展示
+------+--------+
| s_id | s_name |
+------+--------+
| 13   | 黄蓉   |
| 14   | 郭靖   |
+------+--------+