Fork me on GitHub

MySQL50-12-第46-50题

MySQL50-12-第46-50题

本文中介绍的是第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实现

自己的方法
1
2
3
4
5
6
7
-- 自己的方法

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;

参考方法
1
2
3
4
5
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函数能够指定返回的数据

1
2
3
4
-- 两个方法

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

返回具体的日期:

题目47

题目需求

查询本周过生日的学生

分析过程

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

1
select week(now());   -- 47
  1. DAYOFWEEK(date)

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

  1. WEEKDAY(date)

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

SQL实现

自己的方法
1
2
3
select *
from Student
where week(s_birth) = week(now());

参考方法
1
2
3
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实现

1
2
3
4
5
6
7
-- 自己的方法
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);

边界问题

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

1
2
3
4
-- 自己的方法

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

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

题目49

题目需求

查询本月过生的同学

分析过程

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

SQL实现

1
2
3
4
5
6
7
-- 自己的方法
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实现

1
2
3
4
5
6
7
-- 自己的方法
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月份,我们如何解决???将上面的代码进行改进:

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

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

本文标题:MySQL50-12-第46-50题

发布时间:2020年11月22日 - 13:11

原始链接:http://www.renpeter.cn/2020/11/22/MySQL50-12-%E7%AC%AC46-50%E9%A2%98.html

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

Coffee or Tea