SQL 打印全年日历
上文我们实现了“打印一个月日历”的需求，今天在原来的基础实现一个更复杂的需求：打印一年的日历。
其实，打印一年的日历和打印一个月的实现思路很相似，本质上都是通过行转列实现，只不过年历要考虑的东西多一点。
如上图，如果我们要输出 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()语法，将每一步做的工作清晰地呈现出来；- 使用开窗函数。用会话变量的方式实现了开窗函数显得比较笨拙，且更复杂；
- 支持动态行转列。
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2020-01-08，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。