最近偶然发现这位朋友 @carolinezhq 分享的sql练习题carolinezhq：【SQL】SQL面试50题 分类梳理与解答zhuanlan.zhihu.com
其中最后的52题比较有意思(写出52题，那53题就不成问题了)，题目大意是：从学生信息表(student)中通过出生日期字段(sage)查询出本周过生日的同学。相关表如下图：
这个题目对于我这个mysql新手来说还是比较棘手的，百度一翻之后发现很多答案要么是错的，要么写得我一脸懵逼，最后决定还是自己动手丰衣足食。
解题大体思路：判断生日日期与当前日期是否为本年度相同周数
两个注意点(也是难点)：1.同周跨年(即上一年的12月31号与下一年的1月1号在同一个星期)，2.特殊的2月29号生日(对于这一点我假定在非闰年时，这类同学是在3月1号过生日)
我的方法是先创建函数对特殊情况进行分类讨论，全部语句如下：
delimiter //
create function birthday(sage date)
returns boolean NOT DETERMINISTIC NO SQL
begin
declare date_now date;
declare bool boolean;
set date_now = curdate();
if month(sage)=12 and month(date_now)=1 then
if week(replace(sage, year(sage), year(date_now)-1), 7) = week(date_now,7) then
set bool = 1;
else
set bool = 0;
end if;
elseif month(date_now)=12 and month(sage)=1 then
if week(replace(sage, year(sage), year(date_now)+1), 7) = week(date_now,7) then
set bool = 1;
else
set bool = 0;
end if;
elseif month(sage)=2 and day(sage)=29 then
if year(date_now)%4=0 then
if week(replace(sage, year(sage), year(date_now)),7) = week(date_now,7) then
set bool = 1;
else
set bool = 0;
end if;
else
if week(concat_ws('-',year(curdate()),'03','01'),7) = week(date_now,7) then
set bool = 1;
else
set bool = 0;
end if;
end if;
else
if week(replace(sage, year(sage), year(date_now)),7) = week(date_now,7) then
set bool = 1;
else
set bool = 0;
end if;
end if;
return bool;
end//
delimiter ;
select sno, sname, sage, ssex
from (select *, birthday(sage) as bool from student) as a
where a.bool = 1;
如果要查询下周过生日的学生，可以在获取的当前日期上再加7天。
set date_now = date_add(curdate(), interval 1 week);
补充20题的答案：
--20. 查询和" 01 "号同学学习的课程完全相同的其他同学的信息
--筛选出和01号同学有相同课程数量的其他同学及课程数
create view cno_frequency as
select sno, count(cno) as cno_num from sc
where sno <> '01'
group by sno
having count(cno) = (select count(cno) from sc where sno='01');
--统计其他同学和01号同学相同课程的课程数
create view sno_frequency as
select sno, count(cno) as sno_num from sc
where cno in (select cno from sc where sno = '01')
and sno <> '01'
group by sno;
select * from student
where sno in (select s.sno
from sno_frequency as s left outer join cno_frequency as c
on s.sno = c.sno
where s.sno_num =c.cno_num);