SQL 打印全年日历

SQL实现 2021-04-29

1257

上文我们实现了“打印一个月日历”的需求，今天在原来的基础实现一个更复杂的需求：打印一年的日历。

其实，打印一年的日历和打印一个月的实现思路很相似，本质上都是通过行转列实现，只不过年历要考虑的东西多一点。

如上图，如果我们要输出 4 x 3 版式的年历，要考虑哪些地方？

观察图片可知，这里的年历的月份和上文的月历不同的地方是星期天放在一周的前面，因此获取日期所在周的函数的参数要调整。
1月、2月、3月在第一队，4月、5月、6月在第二队，7月、8月、9月在第三队，10月、11月、12月在第四队。我们暂时把一个月的数据当成一个整体，通过公式 n = FLOOR((当前月 - 1) 3)
计算出每个月份被放在第几队。
同一队里面每个月份的同一周的数据都处于同一排。每个月最多只有 5 周，我们能计算出在一个月里面每个日期分别处于哪一周。由于 WEEK
函数计算的是当前日期在一年里面处于第几周，要计算 1 月以后每个日期在它所在的月份里面属于第几周，可通过公式 WEEK(当前日期)-WEEK(上个月最后一天的日期)
得到。比如 WEEK('2020-01-31') = 4
，WEEK('2020-02-01') = 4
，那么 2020-02-01 这个日期在 2 月份里面就在第 0 周。

具体实现的 SQL 如下：

SET @someday := CURDATE();
SELECT 
  MAX(IF(m % 3 = 1 
      AND row_no = 0, m, '')) AS '月份',
  MAX(IF(m % 3 = 1, Sun, '')) AS '日',
  MAX(IF(m % 3 = 1, Mon, '')) AS '一',
  MAX(IF(m % 3 = 1, Tue, '')) AS '二',
  MAX(IF(m % 3 = 1, Wed, '')) AS '三',
  MAX(IF(m % 3 = 1, Thur, '')) AS '四',
  MAX(IF(m % 3 = 1, Fri, '')) AS '五',
  MAX(IF(m % 3 = 1, Sat, '')) AS '六',
  MAX(IF(m % 3 = 2 
      AND row_no = 0, m, '')) AS '月份',
  MAX(IF(m % 3 = 2, Sun, '')) AS '日',
  MAX(IF(m % 3 = 2, Mon, '')) AS '一',
  MAX(IF(m % 3 = 2, Tue, '')) AS '二',
  MAX(IF(m % 3 = 2, Wed, '')) AS '三',
  MAX(IF(m % 3 = 2, Thur, '')) AS '四',
  MAX(IF(m % 3 = 2, Fri, '')) AS '五',
  MAX(IF(m % 3 = 2, Sat, '')) AS '六',
  MAX(IF(m % 3 = 0 
      AND row_no = 0, m, '')) AS '月份',
  MAX(IF(m % 3 = 0, Sun, '')) AS '日',
  MAX(IF(m % 3 = 0, Mon, '')) AS '一',
  MAX(IF(m % 3 = 0, Tue, '')) AS '二',
  MAX(IF(m % 3 = 0, Wed, '')) AS '三',
  MAX(IF(m % 3 = 0, Thur, '')) AS '四',
  MAX(IF(m % 3 = 0, Fri, '')) AS '五',
  MAX(IF(m % 3 = 0, Sat, '')) AS '六' 
FROM
  (
  /* 使用会话变量实现开窗函数的功能，计算每个日期在所在月份里面的相对周 */
  SELECT 
    flag,
    m,
    wk,
    Mon,
    Tue,
    Wed,
    Thur,
    Fri,
    Sat,
    Sun,
    @wk_month := IF(m = @month, @wk_month, wk),
    wk - @wk_month AS row_no,
    @month := m,
    @week := wk 
  FROM
    (
    /* 把月份当成一个整体，所有的月份的数据都在同一“列”*/
    SELECT 
      flag,
      m,
      wk,
      MAX(IF(wkday = 0, day_index, '')) AS 'Mon',
      MAX(IF(wkday = 1, day_index, '')) AS 'Tue',
      MAX(IF(wkday = 2, day_index, '')) AS 'Wed',
      MAX(IF(wkday = 3, day_index, '')) AS 'Thur',
      MAX(IF(wkday = 4, day_index, '')) AS 'Fri',
      MAX(IF(wkday = 5, day_index, '')) AS 'Sat',
      MAX(IF(wkday = 6, day_index, '')) AS 'Sun' 
    FROM
      (
      /* 计算每个日期所在的队、月份、周、星期几*/
      SELECT 
        FLOOR((MONTH(day_m) - 1) / 3) AS flag,
        MONTH(day_m) AS m,
        WEEK(day_m) 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 
            STR_TO_DATE(
              CONCAT(YEAR(@someday), '-01-01'),
              '%Y-%m-%d'
            ) AS first_day,
            STR_TO_DATE(
              CONCAT(YEAR(@someday), '-12-31'),
              '%Y-%m-%d'
            ) AS last_day_year) a,
          t_seq t 
        WHERE t.id <= 1+ DATEDIFF(last_day_year, first_day)) b) c 
    GROUP BY flag,
      m,
      wk) d,
    (SELECT 
      @month := 1,
      @week := 0,
      @wk_month := 0) t 
  WHERE 1 = 1) e 
GROUP BY flag,
  row_no

输出的效果图，我用蓝色的画笔圈出了月份。

说些不足的地方，SQL 写了一坨，理解起来会有点费劲，这也和使用的数据库工具有关系。我使用的 MySQL 5.6 版本，如果在该版本里面就支持使用一些新语法、函数，结果会更好。

使用 with x as()
语法，将每一步做的工作清晰地呈现出来；
使用开窗函数。用会话变量的方式实现了开窗函数显得比较笨拙，且更复杂；
支持动态行转列。

数据库

文章转载自SQL实现，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

SQL 打印全年日历

评论