如何将带WITH子句的SQL语句转换为适配全版本MySQL（含8.0前）的代码？

原代码核心功能：生成指定时间区间[@start, @end]内每个自然月的月份名称、当月总天数，结果存入RESULT_TABLE表

适配思路 #

• MySQL 8.0以下版本不支持递归CTE，改用连续数字序列构造的方式生成月份序列
• 替换SQL Server专属函数为MySQL兼容语法：
  • 原DATEADD替换为MySQL的DATE_ADD/DATE_SUB
  • 原DATENAME(MONTH, dt)替换为MONTHNAME(dt)
  • 原SELECT ... INTO 表替换为兼容性更好的CREATE TABLE ... AS SELECT写法

完整可运行代码 #

-- 定义时间边界参数
SET @start  = '20210101';
SET @end  = '20211231';

-- 清理旧表（可选，按需开启）
DROP TABLE IF EXISTS RESULT_TABLE;

-- 生成结果表
CREATE TABLE RESULT_TABLE AS
SELECT 
  MONTHNAME(month_start) AS `Name`,
  DAY(LAST_DAY(month_start)) AS `Days`
FROM (
  -- 构造0~99的连续数字，默认支持最长100个月的时间区间，可按需扩展
  SELECT 
    DATE_ADD(
      DATE_SUB(@start, INTERVAL (DAY(@start) - 1) DAY), 
      INTERVAL t.n MONTH
    ) AS month_start
  FROM (
    SELECT a.N + b.N * 10 AS n
    FROM 
      (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
      (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
  ) t
  WHERE 
    DATE_ADD(DATE_SUB(@start, INTERVAL (DAY(@start) - 1) DAY), INTERVAL t.n MONTH) 
    <= DATE_SUB(@end, INTERVAL (DAY(@end) - 1) DAY)
) tmp;

扩展说明 #

如果你的查询时间区间超过100个月，可通过叠加笛卡尔积扩展数字序列范围，比如新增一层c表可将范围扩展到1000个月：

SELECT a.N + b.N * 10 + c.N * 100 AS n
FROM 
  (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
  (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b,
  (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c

内容的提问来源于stack exchange，提问作者scripter2021