MySQL 打印本月日历

末蓝、 2022-12-19 00:42 142阅读 0赞

备注:测试数据库版本为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生成连续数字][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)

[MySQL]: https://blog.csdn.net/u010520724/article/details/109113113