如何改写Oracle当月日期查询以同时兼容Oracle与H2数据库？

要实现同时在Oracle和H2中生成当月所有日期的列表，核心是用**递归CTE（公共表表达式）**替代Oracle专属的CONNECT BY LEVEL语法，同时使用两个数据库都支持的日期函数替换Oracle独有的TRUNC。

通用递归CTE方案 #

递归CTE是Oracle 11g+和H2都支持的标准语法，能完美替代原查询的逻辑：

WITH date_range (mydate, last_day) AS (
    -- 锚点：获取当月第一天和当月最后一天
    SELECT
        DATE_TRUNC('MONTH', CURRENT_DATE) AS mydate,
        LAST_DAY(CURRENT_DATE) AS last_day
    FROM DUAL
    UNION ALL
    -- 递归：逐天生成下一个日期，直到当月最后一天
    SELECT
        mydate + INTERVAL '1' DAY,
        last_day
    FROM date_range
    WHERE mydate < last_day
)
SELECT mydate FROM date_range;

兼容性说明 #

• DATE_TRUNC('MONTH', CURRENT_DATE)：等价于Oracle的TRUNC(CURRENT_DATE, 'MM')，两个数据库都支持，用于获取当月第一天。
• LAST_DAY(CURRENT_DATE)：Oracle和H2均支持，直接获取当月最后一天。
• 递归CTE结构：替代Oracle的CONNECT BY LEVEL逻辑，通过递归逐天生成日期。
• DUAL虚拟表：H2同样支持该表，无需修改。

兼容旧版H2的备选写法 #

如果使用的H2版本不支持INTERVAL语法，可以用DATEADD函数替换日期递增逻辑：

WITH date_range (mydate, last_day) AS (
    SELECT
        DATE_TRUNC('MONTH', CURRENT_DATE) AS mydate,
        LAST_DAY(CURRENT_DATE) AS last_day
    FROM DUAL
    UNION ALL
    SELECT
        DATEADD('DAY', 1, mydate),
        last_day
    FROM date_range
    WHERE mydate < last_day
)
SELECT mydate FROM date_range;

验证说明 #

• 在Oracle中运行：递归CTE会被数据库优化，性能与原CONNECT BY写法相当。
• 在H2中运行：无需依赖LEVEL、CONNECT BY等Oracle专属语法，完全符合H2的SQL标准。

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