问题背景 #

给定起始和结束日期（'20180504' 和 '20180425'），需要写出SQL查询返回该区间内的所有连续日期，并且保持YYYYMMDD的字符串格式，期望结果如下：

'20180504' '20180503' '20180502' '20180501' '20180430' '20180429' '20180428' '20180427' '20180426' '20180425'

主流数据库的最佳实现方案 #

因为不同SQL方言的语法差异，下面针对常用数据库分别给出高效的解决方案：

MySQL/MariaDB

如果是MySQL 8.0及以上版本，递归CTE是最简洁的方式，无需依赖额外的系统表：

WITH RECURSIVE date_series AS (
    -- 初始日期：范围的结束日期（要倒序输出的话从大的日期开始）
    SELECT STR_TO_DATE('20180504', '%Y%m%d') AS date_val
    UNION ALL
    -- 每次往前推1天
    SELECT DATE_SUB(date_val, INTERVAL 1 DAY)
    FROM date_series
    -- 直到达到起始日期
    WHERE date_val > STR_TO_DATE('20180425', '%Y%m%d')
)
-- 格式化为YYYYMMDD字符串输出
SELECT DATE_FORMAT(date_val, '%Y%m%d') AS date_str
FROM date_series;

如果是MySQL 5.x版本，不支持递归CTE，可以借助系统表生成数字序列来实现：

SELECT DATE_FORMAT(
    DATE_SUB(STR_TO_DATE('20180504', '%Y%m%d'), INTERVAL (num-1) DAY),
    '%Y%m%d'
) AS date_str
FROM (
    -- 生成足够多的数字（这里生成1000条足够覆盖大部分日期范围）
    SELECT @row := @row + 1 AS num 
    FROM information_schema.tables, (SELECT @row := 0) init
    LIMIT 1000
) nums
WHERE DATE_SUB(STR_TO_DATE('20180504', '%Y%m%d'), INTERVAL (num-1) DAY) 
      >= STR_TO_DATE('20180425', '%Y%m%d');

PostgreSQL

PostgreSQL自带的generate_series函数天生适合生成序列，写法非常简洁：

-- 生成从2018-04-25到2018-05-04的连续日期，然后格式化为YYYYMMDD，再倒序排列
SELECT TO_CHAR(date_val, 'YYYYMMDD') AS date_str
FROM generate_series(
    '2018-04-25'::DATE, 
    '2018-05-04'::DATE, 
    '1 day'
) AS date_val
ORDER BY date_str DESC;

SQL Server

同样可以用递归CTE来实现，注意如果日期范围超过100天需要加上OPTION (MAXRECURSION 0)：

WITH date_series AS (
    SELECT CAST('20180504' AS DATE) AS date_val
    UNION ALL
    SELECT DATEADD(DAY, -1, date_val)
    FROM date_series
    WHERE date_val > CAST('20180425' AS DATE)
)
SELECT FORMAT(date_val, 'yyyyMMdd') AS date_str
FROM date_series
OPTION (MAXRECURSION 0);

核心要点总结 #

• 先将输入的YYYYMMDD字符串转换为数据库的日期类型，避免字符串直接计算带来的错误
• 优先使用数据库原生的序列生成功能（比如PostgreSQL的generate_series、MySQL/SQL Server的递归CTE），性能和可读性都更好
• 最后通过格式化函数将日期类型转换回YYYYMMDD格式，方便和其他表的数据做关联查询

内容的提问来源于stack exchange，提问作者A.DS