SQL 打印一个月的日历

白日梦想家

发布于 2020-07-20 10:04:30

1.3K0

发布于 2020-07-20 10:04:30

文章被收录于专栏：SQL实现SQL实现

今天，我们用 SQL 做一件有趣的东西：打印一个月的日历。

下图是我从电脑上截的本月的日历。

接下来我们在 MYSQL 上输出这个效果。

大致的思路如下：

获取指定日期所在月份的第一天的日期和该月的天数；
生成该月的所有日期集合；
格式化输出。

1 获取月初第一天和该月的天数

在 MySQL 里面，实现日期的加减可以使用 DATE_ADD(date,INTERVAL expr unit) / DATE_SUB(date,INTERVAL expr unit) 函数。

另外，还可以用 LAST_DAY(date) 获取最后一天的日期。

# 设置日期变量
SET @someday:=CURDATE();
# 获取该月第一天
SELECT DATE_ADD(@someday,INTERVAL - DAY(@someday) + 1 DAY) 
# 获取该月的天数
SELECT DAY(LAST_DAY(@someday))

2 生成所在月的日期集合

MySQL 暂时没有提供像 Oracle 的start with connect by prior 一样的语法，用它可以递归生成一批简单的测试数据集，但我们可以借助数字辅助表实现该功能。

我们用到了数字辅助表 t_seq，t_seq 的表结构很简单，只有一个整数字段，里面存储了从 1 - 1000 的自然数。

t_seq 的表结构

CREATE TABLE `t_seq` (
  `id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

生成一个月的所有日期的集合

SELECT 
    WEEK(day_m, 1) AS wk,
    WEEKDAY(day_m) AS wkday,
    DAY(day_m) AS day_index,
    day_m AS full_day 
  FROM
    (SELECT 
      DATE_ADD(first_day, INTERVAL id - 1 DAY) AS day_m 
    FROM
      (SELECT 
        DATE_ADD(
          @someday,
          INTERVAL - DAY(@someday) + 1 DAY
        ) AS first_day) a,
      t_seq t 
    WHERE t.id <= DAY(LAST_DAY(@someday))

3 格式化日历

我们在第 2 步生成的数据集只有一列，要输出日历的效果，还得做一层行转列操作：根据每周做分组，星期一到星期天作为列，将一列转成四行七列或者五行七列的格式。

MySQL 提供了 WEEK(date[,mode]) 函数获取每周的编号，传入不同的 mode 参数返回的数据会不一样。

Mode	First day of week	Range	Week 1 is the first week …
0	Sunday	0-53	with a Sunday in this year
1	Monday	0-53	with 4 or more days this year
2	Sunday	1-53	with a Sunday in this year
3	Monday	1-53	with 4 or more days this year
4	Sunday	0-53	with 4 or more days this year
5	Monday	0-53	with a Monday in this year
6	Sunday	1-53	with 4 or more days this year
7	Monday	1-53	with a Monday in this year

由于我们把星期一看作一周的第一天，所以 mode 只能选 1 和 5。

完整的 SQL 实现如下：

SET @someday := CURDATE();
SELECT 
  MAX(IF(wkday = 0, day_index, '')) AS '一',
  MAX(IF(wkday = 1, day_index, '')) AS '二',
  MAX(IF(wkday = 2, day_index, '')) AS '三',
  MAX(IF(wkday = 3, day_index, '')) AS '四',
  MAX(IF(wkday = 4, day_index, '')) AS '五',
  MAX(IF(wkday = 5, day_index, '')) AS '六',
  MAX(IF(wkday = 6, day_index, '')) AS '日' 
FROM
  (SELECT 
    WEEK(day_m, 1) AS wk,
    WEEKDAY(day_m) AS wkday,
    DAY(day_m) AS day_index,
    day_m AS full_day 
  FROM
    (SELECT 
      DATE_ADD(first_day, INTERVAL id - 1 DAY) AS day_m 
    FROM
      (SELECT 
        DATE_ADD(
          @someday,
          INTERVAL - DAY(@someday) + 1 DAY
        ) AS first_day) a,
      t_seq t 
    WHERE t.id <= DAY(LAST_DAY(@someday))) b) c 
GROUP BY wk

最终的效果图