获取日期列表:
SELECT TO_CHAR(TO_DATE('2014-10-01', 'yyyy-MM-dd') + ROWNUM - 1, 'yyyyMMdd') as daylist
FROM DUAL
CONNECT BY ROWNUM <=
trunc(to_date('2015-06-01', 'yyyy-MM-dd') -
to_date('2014-10-01', 'yyyy-MM-dd')) + 1
获取月份列表:
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2014-10', 'yyyy-MM'), ROWNUM - 1),
'yyyyMM') as monthlist
FROM DUAL
CONNECT BY ROWNUM <=
months_between(to_date('2015-06', 'yyyy-MM'),
to_date('2014-10', 'yyyy-MM')) + 1
获取周列表: 获取自然周 以周一开始 - 周日结束
SELECT trunc(to_DATE('2018-09-02', 'YYYY-MM-DD') + (ROWNUM - 1) * 7, 'iw') AS mon,
trunc(to_DATE('2018-09-02', 'YYYY-MM-DD') + (ROWNUM - 1) * 7, 'iw') + 6 AS sun
FROM DUAL
CONNECT BY ROWNUM <=
(trunc(to_DATE('2018-10-31', 'YYYY-MM-DD'), 'iw') + 6 -
trunc(to_DATE('2018-09-02', 'YYYY-MM-DD'), 'iw')) / 7 + 1
获取年份列表:
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2014-10', 'yyyy-MM'), (ROWNUM - 1) * 12),
'yyyy') as yearlist
FROM DUAL
CONNECT BY ROWNUM <=
months_between(to_date('2015-06', 'yyyy-MM'),
to_date('2014-10', 'yyyy-MM')) / 12 + 1
分组统计,按照天、月份周和自然周、月、季度和年
https://blog.csdn.net/JavaAlpha/article/details/52277270
-- 按自然周的日期统计
select to_char(next_day(t.CREATED+15/24 - 7,2),'YYYY-MM-DD') AS 周,sum(1) as 数量
from TB_EXT_TRADE t
WHERE
t.TID LIKE 'SC%' OR t.TID LIKE 'WSC%'
group by to_char(next_day(t.CREATED+15/24 - 7,2),'YYYY-MM-DD')
ORDER BY 周;
分组统计周 自然周 select to_char(next_day(to_DATE('2018-09-02', 'YYYY-MM-DD') + 15 / 24 - 7, 2),'YYYY-MM-DD') from dual; 和 select trunc(to_DATE('2018-09-02', 'YYYY-MM-DD'),'iw') from dual; 取到的值 是一样的 获取该日期所在自然周的周一 自然周 以周一开始 周 日 结束