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;
如果不想用distinct,当然也可以用group by来实现。
错误实现方式:
使用months_between()计算两个日期间的间隔月数+1,其值为6,这个没有问题;
问题处在了:TO_CHAR(TO_DATE('2020-01', 'YYYY-MM') + ROWNUM - 1, 'YYYY-MM'),我们将日期格式化到天,看一下
日期+数值,代表的含义是:往指定日期后叠加多少天,也就是不管你数值有多大n,都按天数来计算,并推算出n天后对应的日期。
这样一来,自然是不能再使用months_between()来计算,即使,这个函数本身能够计算出间隔月数。
3.3 查询出间隔的年份列表
明白了天数和月数的查询计算方式,这个也就是直接套模板的事儿了。
3.4 查询月初和月末所对应的日期
SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE) + 1, -LEVEL - 1), 'YYYYMMDD') AS BEGINTIME, TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE), -LEVEL), 'YYYYMMDD') ENDTIME FROM DUAL CONNECT BY LEVEL < 13
解说:
现在是2020年6月17日,last_day(),求出6月最后一天是30日,+1,得到的是7月1号,假设现在level=1,add_months(7月1日,-2),也就是5月1日;
ADD_MONTHS(LAST_DAY(SYSDATE), -LEVEL),也就是add_months(6月30日,-1),也就是5月31日(一个月前)。