如何在SQL中生成跨年度递增季度、循环月份及递增年份的输出？

规则说明 #

• Year：从2012开始，每12行递增1
• Month：循环1~12，每12行后重置为1
• Quarter：每3行递增1，跨年度不中断（2012年最后为4，2013年起始为5）

实现思路 #

通过生成连续行号，基于行号计算三个字段：

1. 行号n从1开始
2. Year = 2012 + FLOOR((n-1)/12)
3. Month = ((n-1) % 12) + 1
4. Quarter = FLOOR((n-1)/3) + 1

各数据库实现代码 #

MySQL/MariaDB（8.0+）

WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 24 -- 24行对应2年数据
)
SELECT
    2012 + FLOOR((n - 1) / 12) AS Year,
    ((n - 1) % 12) + 1 AS Month,
    FLOOR((n - 1) / 3) + 1 AS Quarter
FROM seq
ORDER BY n;

PostgreSQL

-- 方法1：递归CTE
WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 24
)
SELECT
    2012 + (n - 1) / 12 AS Year,
    ((n - 1) % 12) + 1 AS Month,
    (n - 1) / 3 + 1 AS Quarter
FROM seq
ORDER BY n;

-- 方法2：使用generate_series更简洁
SELECT
    2012 + (s - 1) / 12 AS Year,
    ((s - 1) % 12) + 1 AS Month,
    (s - 1) / 3 + 1 AS Quarter
FROM generate_series(1, 24) AS s
ORDER BY s;

SQL Server

WITH seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 24
)
SELECT
    2012 + FLOOR((n - 1) / 12) AS Year,
    ((n - 1) % 12) + 1 AS Month,
    FLOOR((n - 1) / 3) + 1 AS Quarter
FROM seq
ORDER BY n
OPTION (MAXRECURSION 0); -- 递归行数超过100时需添加此选项

输出结果 #

执行上述脚本后，将生成与需求完全匹配的数据集：

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