MySQL 打印本月日历

最新推荐文章于 2023-10-13 19:20:22 发布

只是甲

最新推荐文章于 2023-10-13 19:20:22 发布

阅读量895

点赞数

分类专栏： # MySQL CookBook MySQL从小工到专家之路文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/u010520724/article/details/109495960

版权

MySQL从小工到专家之路同时被 2 个专栏收录

181 篇文章 23 订阅

订阅专栏

MySQL CookBook

73 篇文章 3 订阅

订阅专栏

备注:测试数据库版本为MySQL 8.0

文章目录

一. 需求:
二.解决方案
- 2.1 MySQL 8.0写法
- 2.2 MySQL8.0之前写法

一. 需求:

为当前月创建一个日历。日历的格式应该与书桌上的日历相似，通常情况下包含7列、5行。

二.解决方案

每个解决方案看起来都有所不同，但他们都采用了同样方式来解决问题:
返回当前月的每一天，然后依据当前月中每周的周内日期创建日历。

日历可采用不同的格式。例如Unix的call命令采用从星期日到星期六的格式。
本例都以ISO周序号为基准，因此采用了星期一到星期五的格式最方便。
一旦习惯了这种解决方案，就会明白，无论怎么重新定义格式都是非常简单的事，只需修改由ISO周规定的值即可。

2.1 MySQL 8.0写法

with RECURSIVE c(n) as
-- 构造一个1-40的临时表
 (select 1   union all select n + 1 from c where n < 40),
-- 求出本月的第一天
firstday AS
  ( SELECT adddate(current_date,-dayofmonth(current_date)+1) fd),
-- 求出本月的每个日期及星期数
y AS
(
SELECT adddate(fd,c.n -1) yd,
       dayofweek(adddate(fd,c.n -1)) wk,
       month(adddate(fd,c.n -1)) mth,
       weekofyear(adddate(fd,c.n -1)) yk
from c,firstday
where month(adddate(fd,c.n -1)) = month(fd)
)
SELECT max(case when wk = 2 then DATE_FORMAT(yd,'%d') else null end)  Mo,
       max(case when wk = 3 then DATE_FORMAT(yd,'%d') else null end)  Tu,
       max(case when wk = 4 then DATE_FORMAT(yd,'%d') else null end)  We,
       max(case when wk = 5 then DATE_FORMAT(yd,'%d') else null end)  Th,
       max(case when wk = 6 then DATE_FORMAT(yd,'%d') else null end)  Fr,
       max(case when wk = 7 then DATE_FORMAT(yd,'%d') else null end)  Sa,
       max(case when wk = 1 then DATE_FORMAT(yd,'%d') else null end)  Su
from y
group by yk

测试记录:

mysql> with RECURSIVE c(n) as
    -> -- 构造一个1-40的临时表
    ->  (select 1   union all select n + 1 from c where n < 40),
    -> -- 求出本月的第一天
    -> firstday AS
    ->   ( SELECT adddate(current_date,-dayofmonth(current_date)+1) fd),
    -> -- 求出本月的每个日期及星期数
    -> y AS
    -> (
    -> SELECT adddate(fd,c.n -1) yd,
    ->        dayofweek(adddate(fd,c.n -1)) wk,
    ->        month(adddate(fd,c.n -1)) mth,
    ->        weekofyear(adddate(fd,c.n -1)) yk
    -> from c,firstday
    -> where month(adddate(fd,c.n -1)) = month(fd)
    -> )
    -> SELECT max(case when wk = 2 then DATE_FORMAT(yd,'%d') else null end)  Mo,
    ->        max(case when wk = 3 then DATE_FORMAT(yd,'%d') else null end)  Tu,
    ->        max(case when wk = 4 then DATE_FORMAT(yd,'%d') else null end)  We,
    ->        max(case when wk = 5 then DATE_FORMAT(yd,'%d') else null end)  Th,
    ->        max(case when wk = 6 then DATE_FORMAT(yd,'%d') else null end)  Fr,
    ->        max(case when wk = 7 then DATE_FORMAT(yd,'%d') else null end)  Sa,
    ->        max(case when wk = 1 then DATE_FORMAT(yd,'%d') else null end)  Su
    -> from y
    -> group by yk;
+------+------+------+------+------+------+------+
| Mo   | Tu   | We   | Th   | Fr   | Sa   | Su   |
+------+------+------+------+------+------+------+
| NULL | NULL | NULL | 01   | 02   | 03   | 04   |
| 05   | 06   | 07   | 08   | 09   | 10   | 11   |
| 12   | 13   | 14   | 15   | 16   | 17   | 18   |
| 19   | 20   | 21   | 22   | 23   | 24   | 25   |
| 26   | 27   | 28   | 29   | 30   | 31   | NULL |
+------+------+------+------+------+------+------+
5 rows in set (0.00 sec)

这样看起来稍微有点复杂，我们拆解开来
1.构造一个1-40的临时表
with RECURSIVE c(n) as
(select 1 union all select n + 1 from c where n < 40)
2. 求出本月的第一天
SELECT adddate(current_date,-dayofmonth(current_date)+1) fd
3.通过1、2 两个步骤构造出本月的月初到月底的每一天
SELECT adddate(fd,c.n -1) yd,
dayofweek(adddate(fd,c.n -1)) wk,
month(adddate(fd,c.n -1)) mth,
weekofyear(adddate(fd,c.n -1)) yk
from c,firstday
where month(adddate(fd,c.n -1)) = month(fd)
4.通过 weekofyear进行分组即可
SELECT max(case when wk = 2 then DATE_FORMAT(yd,’%d’) else null end) Mo,
max(case when wk = 3 then DATE_FORMAT(yd,’%d’) else null end) Tu,
max(case when wk = 4 then DATE_FORMAT(yd,’%d’) else null end) We,
max(case when wk = 5 then DATE_FORMAT(yd,’%d’) else null end) Th,
max(case when wk = 6 then DATE_FORMAT(yd,’%d’) else null end) Fr,
max(case when wk = 7 then DATE_FORMAT(yd,’%d’) else null end) Sa,
max(case when wk = 1 then DATE_FORMAT(yd,’%d’) else null end) Su
from y
group by yk

分解步骤测试记录

mysql> with recursive c(n) as
    -> (
    -> select 1
    -> union ALL
    -> select n + 1
    -> from c
    -> where n < 40
    -> )
    -> select * from c;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
|   11 |
|   12 |
|   13 |
|   14 |
|   15 |
|   16 |
|   17 |
|   18 |
|   19 |
|   20 |
|   21 |
|   22 |
|   23 |
|   24 |
|   25 |
|   26 |
|   27 |
|   28 |
|   29 |
|   30 |
|   31 |
|   32 |
|   33 |
|   34 |
|   35 |
|   36 |
|   37 |
|   38 |
|   39 |
|   40 |
+------+
40 rows in set (0.00 sec)


mysql> SELECT adddate(current_date,-dayofmonth(current_date)+1) fd
    -> ;
+------------+
| fd         |
+------------+
| 2020-10-01 |
+------------+
1 row in set (0.00 sec)


mysql> with RECURSIVE c(n) as
    -> -- 构造一个1-40的临时表
    ->  (select 1   union all select n + 1 from c where n < 40),
    -> -- 求出本月的第一天
    -> firstday AS
    ->   ( SELECT adddate(current_date,-dayofmonth(current_date)+1) fd),
    -> -- 求出本月的每个日期及星期数
    -> y AS
    -> (
    -> SELECT adddate(fd,c.n -1) yd,
    ->        dayofweek(adddate(fd,c.n -1)) wk,
    ->        month(adddate(fd,c.n -1)) mth,
    ->        weekofyear(adddate(fd,c.n -1)) yk
    -> from c,firstday
    -> where month(adddate(fd,c.n -1)) = month(fd)
    -> )
    -> select * from y;
+------------+------+------+------+
| yd         | wk   | mth  | yk   |
+------------+------+------+------+
| 2020-10-01 |    5 |   10 |   40 |
| 2020-10-02 |    6 |   10 |   40 |
| 2020-10-03 |    7 |   10 |   40 |
| 2020-10-04 |    1 |   10 |   40 |
| 2020-10-05 |    2 |   10 |   41 |
| 2020-10-06 |    3 |   10 |   41 |
| 2020-10-07 |    4 |   10 |   41 |
| 2020-10-08 |    5 |   10 |   41 |
| 2020-10-09 |    6 |   10 |   41 |
| 2020-10-10 |    7 |   10 |   41 |
| 2020-10-11 |    1 |   10 |   41 |
| 2020-10-12 |    2 |   10 |   42 |
| 2020-10-13 |    3 |   10 |   42 |
| 2020-10-14 |    4 |   10 |   42 |
| 2020-10-15 |    5 |   10 |   42 |
| 2020-10-16 |    6 |   10 |   42 |
| 2020-10-17 |    7 |   10 |   42 |
| 2020-10-18 |    1 |   10 |   42 |
| 2020-10-19 |    2 |   10 |   43 |
| 2020-10-20 |    3 |   10 |   43 |
| 2020-10-21 |    4 |   10 |   43 |
| 2020-10-22 |    5 |   10 |   43 |
| 2020-10-23 |    6 |   10 |   43 |
| 2020-10-24 |    7 |   10 |   43 |
| 2020-10-25 |    1 |   10 |   43 |
| 2020-10-26 |    2 |   10 |   44 |
| 2020-10-27 |    3 |   10 |   44 |
| 2020-10-28 |    4 |   10 |   44 |
| 2020-10-29 |    5 |   10 |   44 |
| 2020-10-30 |    6 |   10 |   44 |
| 2020-10-31 |    7 |   10 |   44 |
+------------+------+------+------+
31 rows in set (0.00 sec)


mysql> with RECURSIVE c(n) as
    -> -- 构造一个1-40的临时表
    ->  (select 1   union all select n + 1 from c where n < 40),
    -> -- 求出本月的第一天
    -> firstday AS
    ->   ( SELECT adddate(current_date,-dayofmonth(current_date)+1) fd),
    -> -- 求出本月的每个日期及星期数
    -> y AS
    -> (
    -> SELECT adddate(fd,c.n -1) yd,
    ->        dayofweek(adddate(fd,c.n -1)) wk,
    ->        month(adddate(fd,c.n -1)) mth,
    ->        weekofyear(adddate(fd,c.n -1)) yk
    -> from c,firstday
    -> where month(adddate(fd,c.n -1)) = month(fd)
    -> )
    -> SELECT max(case when wk = 2 then DATE_FORMAT(yd,'%d') else null end)  Mo,
    ->        max(case when wk = 3 then DATE_FORMAT(yd,'%d') else null end)  Tu,
    ->        max(case when wk = 4 then DATE_FORMAT(yd,'%d') else null end)  We,
    ->        max(case when wk = 5 then DATE_FORMAT(yd,'%d') else null end)  Th,
    ->        max(case when wk = 6 then DATE_FORMAT(yd,'%d') else null end)  Fr,
    ->        max(case when wk = 7 then DATE_FORMAT(yd,'%d') else null end)  Sa,
    ->        max(case when wk = 1 then DATE_FORMAT(yd,'%d') else null end)  Su
    -> from y
    -> group by yk;
+------+------+------+------+------+------+------+
| Mo   | Tu   | We   | Th   | Fr   | Sa   | Su   |
+------+------+------+------+------+------+------+
| NULL | NULL | NULL | 01   | 02   | 03   | 04   |
| 05   | 06   | 07   | 08   | 09   | 10   | 11   |
| 12   | 13   | 14   | 15   | 16   | 17   | 18   |
| 19   | 20   | 21   | 22   | 23   | 24   | 25   |
| 26   | 27   | 28   | 29   | 30   | 31   | NULL |
+------+------+------+------+------+------+------+
5 rows in set (0.00 sec)

2.2 MySQL8.0之前写法

按照MySQL创建连续数字中的存储过程的方法来创建一个表t包含 1-50
MySQL生成连续数字

具体的解决方案同2.1，区别在于构造连续数字、日期函数、封装临时表

select max(case dw when 2 then dm end) as Mo,
       max(case dw when 3 then dm end) as Tu,
       max(case dw when 4 then dm end) as We,
       max(case dw when 5 then dm end) as Th,
       max(case dw when 6 then dm end) as Fr,
       max(case dw when 7 then dm end) as Sa,
       max(case dw when 1 then dm end) as Su
   from (
select date_format(dy,'%u') wk,
       date_format(dy,'%d') dm,
       date_format(dy,'%w') + 1 dw
   from  (
 select adddate(x.dy,t.id -1) dy,
        x.mth
   from   (
 select adddate(current_date,-dayofmonth(current_date)+1) dy,
        date_format(
             adddate(current_date,
             -dayofmonth(current_date)+1),
             '%m') mth
           ) x,
             t
where t.id <= 31
 and  date_format(adddate(x.dy,t.id -1),'%m') = x.mth
      ) y
      ) z
group by wk
order by wk

测试记录:

mysql> select max(case dw when 2 then dm end) as Mo,
    ->        max(case dw when 3 then dm end) as Tu,
    ->        max(case dw when 4 then dm end) as We,
    ->        max(case dw when 5 then dm end) as Th,
    ->        max(case dw when 6 then dm end) as Fr,
    ->        max(case dw when 7 then dm end) as Sa,
    ->        max(case dw when 1 then dm end) as Su
    ->    from (
    -> select date_format(dy,'%u') wk,
    ->        date_format(dy,'%d') dm,
    ->        date_format(dy,'%w') + 1 dw
    ->    from  (
    ->  select adddate(x.dy,t.id -1) dy,
    ->         x.mth
    ->    from   (
    ->  select adddate(current_date,-dayofmonth(current_date)+1) dy,
    ->         date_format(
    ->              adddate(current_date,
    ->              -dayofmonth(current_date)+1),
    ->              '%m') mth
    ->            ) x,
    ->              t
    -> where t.id <= 31
    ->  and  date_format(adddate(x.dy,t.id -1),'%m') = x.mth
    ->       ) y
    ->       ) z
    -> group by wk
    -> order by wk;
+------+------+------+------+------+------+------+
| Mo   | Tu   | We   | Th   | Fr   | Sa   | Su   |
+------+------+------+------+------+------+------+
| NULL | NULL | NULL | 01   | 02   | 03   | 04   |
| 05   | 06   | 07   | 08   | 09   | 10   | 11   |
| 12   | 13   | 14   | 15   | 16   | 17   | 18   |
| 19   | 20   | 21   | 22   | 23   | 24   | 25   |
| 26   | 27   | 28   | 29   | 30   | 31   | NULL |
+------+------+------+------+------+------+------+
5 rows in set (0.00 sec)

只是甲

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL 打印本月日历

备注:测试数据库版本为MySQL 8.0文章目录一. 需求:二.解决方案2.1 MySQL 8.0写法2.2 MySQL8.0之前写法一. 需求:为当前月创建一个日历。日历的格式应该与书桌上的日历相似，通常情况下包含7列、5行。二.解决方案每个解决方案看起来都有所不同，但他们都采用了同样方式来解决问题:返回当前月的每一天，然后依据当前月中每周的周内日期创建日历。日历可采用不同的格式。例如Unix的call命令采用从星期日到星期六的格式。本例都以ISO周序号为基准，因此采用了星期一到星期五的格
复制链接

扫一扫