Oracle单条SQL如何查询两个日期区间内的所有月份最后一天
单条SQL实现区间内所有月份最后一天查询
核心优化实现
原有逻辑的问题在于生成全量连续自然日的记录存在大量冗余,结合LAST_DAY函数时无需遍历每一天,可直接按区间覆盖的月份数生成对应层级的结果,执行效率更高。
可直接运行的单条SQL如下:
SELECT TO_CHAR( LAST_DAY(ADD_MONTHS(DATE '2000-01-01', LEVEL - 1)), 'YYYYMMDD' ) AS month_last_day FROM dual CONNECT BY LEVEL <= -- 直接计算两个日期间的总覆盖月份数 MONTHS_BETWEEN(DATE '2050-12-31', DATE '2000-01-01') + 1 ORDER BY LEVEL;
代码逻辑说明
- 用
ADD_MONTHS从起始日期开始,每次偏移1个自然月,替代原来逐日累加的逻辑 - 对每个偏移后的日期套用
LAST_DAY函数,直接取对应月份的最后一天 - 通过
TO_CHAR按YYYYMMDD格式格式化日期,直接输出示例要求的20000131、20000228格式的结果 - 借助Oracle内置的
MONTHS_BETWEEN函数快速计算区间覆盖的总月份数,无需手动拆分年、月做差值计算
适配原有代码逻辑的版本
如果需要完全沿用你现有逐日生成日期的实现思路,可通过对全量日期取月末值后去重实现,写法如下:
SELECT DISTINCT TO_CHAR(LAST_DAY(jour), 'YYYYMMDD') AS month_last_day FROM ( SELECT to_date('01/01/2000','dd/mm/yyyy') + (LEVEL-1) as jour FROM dual CONNECT BY LEVEL <= to_date('31/12/2050','dd/mm/yyyy') - to_date('01/01/2000','dd/mm/yyyy') + 1 ) ORDER BY month_last_day;
该版本的性能缺陷较明显:2000年-2050年的查询区间内,子查询会生成近1.9万条自然日记录,最终仅能去重得到612条月末结果,存在大量无效的计算开销。同区间下按月份生成的写法仅需生成612行有效记录,无冗余计算。
内容的提问来源于stack exchange,提问作者Tyvain
相关产品推荐
相关产品推荐

