终于打卡结束：MySQL经典50题

公众号：尤而小屋
作者：Peter
编辑：Peter

数据分析师=7大主题，24份资料大家好，我是Peter~

MySQL50题终于要结束了：今天带来的是最后5道题：第46-50题。主要的知识点：各种时间和日期函数的使用

• year()
  ：返回年份
• date_format(now(), '%Y%m%d')
  ：返回年月日
• dayofyear()
  ：一年中的第几天
• weekofyear()
  ：一年中的第几周
• week()
  ：一年中的第几周
• month()
  ：返回月份
• dayofweek()
  ：星期索引，1代表星期1
• weekday()
  ：星期索引，0代表星期1

5个题目分别是：

• 查询各学生的年龄：按照出生日期来算，当前月日 < 出生年月的月日则，年龄减1
• 查询本周过生日的学生
• 查询下周过生日的学生
• 查询本月过生日的学生
• 查询下月过生日的学生

题目46

题目需求

查询各学生的年龄：按照出生日期来算，当前月日 < 出生年月的月日则，年龄减1

分析过程

1、我们以出生年月日中的年份来计算年龄，通过year()
来计算当前年份和出生年份的差值

2、比较具体的日期和当前日期的大小，使用dayofyear()
来确定每个出生日期是处在每年的哪一天；如果出生日期靠后，则说明最近这年还没有达到一岁，减去1

3、 使用case
语句来进行判断

参考资料

SQL实现

下面是自己的方法：

-- 自己的方法

select *
 ,case when dayofyear(now()) >= dayofyear(s_birth) then year(now()) - year(s_birth) 
  when dayofyear(now()) < dayofyear(s_birth) then year(now()) - year(s_birth) - 1
 else 'other' end as 'age'
from Student;

复制

下面是参考的方法：

select
 s_name
 ,s_birth
 ,date_format(now(), '%Y') - date_format(s_birth, '%Y') - (case when date_format(now(), '%m%d') > date_format(s_birth, '%m%d') then 0 else 1 end) as age  -- 当前日期大，说明已经过生了，年龄正常；反之说明今年还没有到年龄-1
from Student;

复制

如何返回年份/日期

通过date_format
函数能够指定返回的数据

-- 两个方法

select year(now());
select date_format(now(), '%Y');

复制

返回具体的日期：

题目47

题目需求

查询本周过生日的学生

分析过程

！！！注意：我们通过week函数返回日期在年份中的所属周数

select week(now());   -- 47

复制

1. DAYOFWEEK(date)

返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)，符合国内标准

1. WEEKDAY(date)

返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)，国外标准

SQL实现

下面是自己的方法：

select *
from Student
where week(s_birth) = week(now());

复制

下面是参考的方法：

select * from Student where week(date_format(now(),'%Y%m%d')) = week(s_birth);  -- 方式1

select * from student where yearweek(s_birth) = yearweek(date_format(now(),'%Y%m%d'));   -- 方式2

复制

题目48

题目需求

查询下周过生日的学生

分析过程

本题和上面的题目是类似的，只是需要我们在现有的日期往前推一周

SQL实现

-- 自己的方法
select *
from Student
where week(s_birth) = week(now()) + 1;   -- 往前推1周

-- 参考方法
select * from Student where week(date_format(now(),'%Y%m%d')) + 1= week(s_birth); 

复制

边界问题

如果现在刚好的是今年的最后一个周，那么下周就是明年的第一个周，我们如何解决这个问题呢？？改进后的脚本：

-- 自己的方法

select * from Student
where mod(week(now()), 52) + 1 =  week(s_birth);

复制

当现在刚好是第52周，那么mod函数的结果是0，则说明出生的月份刚好是明年的第一周

题目49

题目需求

查询本月过生的同学

分析过程

我们通过month()
来查询每个日期所在的月份

SQL实现

-- 自己的方法
select *
from Student
where month(s_birth) = month(now());

-- 参考方法
select * from Student where month(date_format(now(), '%Y%m%d')) = month(s_birth);

复制

返回的是空值，是因为数据本身就没有在11月份出生的同学

题目50

题目需求

查询下月过生的同学

分析过程

和上面的题目类似，需要在现有的月份上加1

SQL实现

-- 自己的方法
select * from Student
where month(s_birth) = month(now()) + 1;  -- 推迟一个月

-- 参考方法
select * from Student
where month(date_format(now(), '%Y%m%d')) + 1= month(s_birth);

复制

边界问题

假设现在是12月份，那么下个月就是明年的1月份，我们如何解决？？？将上面的代码进行改进：

select * from Student
where mod(month(now()),12)  + 1 =  month(s_birth);

复制

如果现在是12月份，则mod函数的结果是0，说明生日刚好是1月份