1.情景展示
现在,已知两个日期,根据它俩查询所间隔的所有日期列表,如何实现?
2.原因分析
使用递归查询connect by来实现
3.解决方案
3.1 查询出间隔的天数列表
SELECT TO_CHAR(TO_DATE(‘2020-06-01‘, ‘YYYY-MM-DD‘) + ROWNUM - 1,
‘YYYY-MM-DD‘) AS REGDATE
FROM DUAL
CONNECT BY ROWNUM <=
TRUNC(TO_DATE(‘2020-06-17‘, ‘YYYY-MM-DD‘) -
TO_DATE(‘2020-06-01‘, ‘YYYY-MM-DD‘)) + 1
ORDER BY REGDATE
解说:
trunc(date2-date1),得出的是两个日期间隔的整数位数,比方说17-1=16,而我们要查询的是这两个日期所跨的所有时间,所需需要+1,这样,才能包括开始时间date1和结尾date2;
connect by,准确的来说是递归查询SQL,connect by rownum <=17,也就是需要查询17次;
TO_DATE(‘2020-06-01‘, ‘YYYY-MM-DD‘) + ROWNUM - 1,因为rownum是从1开始的,比方说,第一次查询rownum=1,查询出来的日期是6月1号+1=6月2号,实际上该为6月1号,第二次rownum=2,查询出来的日期是6月1号+2=6月3号,实际为6月2号,。。。所以需要-1。
3.2 查询出间隔的月份列表
--查询两个日期所跨过的月份列表
SELECT DISTINCT REGDATE
FROM (SELECT TO_CHAR(TO_DATE(‘2020-01‘, ‘YYYY-MM‘) + ROWNUM - 1, ‘YYYY-MM‘) AS REGDATE
FROM DUAL
CONNECT BY ROWNUM <= TO_DATE(‘2020-06‘, ‘YYYY-MM‘) -
TO_DATE(‘2020-01‘, ‘YYYY-MM‘) + 1)
ORDER BY REGDATE
两个date日期类型相减,得出的是相差的天数
横跨153天,也就是最终查询结果是153条数据,涵盖了2020-01到2020-06,需要去重,所以,使用distinct
错误实现方式:
使用months_between()计算两个日期间的间隔月数+1,其值为6,这个没有问题;
问题处在了:TO_CHAR(TO_DATE(‘2020-01‘, ‘YYYY-MM‘) + ROWNUM - 1, ‘YYYY-MM‘),我们将日期格式化到天,看一下
日期+数值,代表的含义是:往指定日期后叠加多少天,也就是不管你数值有多大n,都按天数来计算,并推算出n天后对应的日期。
这样一来,自然是不能再使用months_between()来计算,即使,这个函数本身能够计算出间隔月数。
3.3 查询出间隔的年份列表
明白了天数和月数的查询计算方式,这个也就是直接套模板的事儿了。
写在最后
哪位大佬如若发现文章存在纰漏之处或需要补充更多内容,欢迎留言!!!
相关推荐: