orcal 时间分组查询

1.最近7天

SELECT to_char (SYSDATE- LEVEL + 1, 'yyyy-mm-dd') today FROM DUAL connect BY LEVEL <= 7

2.最近30天

SELECT to_date(to_char(trunc(sysdate, 'dd'), 'yyyy-mm-dd'), 'yyyy-mm-dd') - rownum + 1 as day
        FROM dual CONNECT BY ROWNUM <= 30

3.最近6个月

SELECT TO_CHAR(add_months(to_date(to_char(trunc(sysdate, 'mm')+1, 'yyyy-mm'), 'yyyy-mm'), -(ROWNUM - 1)), 'yyyy-mm') as today FROM dual CONNECT BY ROWNUM <= 6 ORDER BY TO_CHAR(add_months(to_date(to_char(trunc(sysdate, 'mm')+1, 'yyyy-mm'), 'yyyy-mm'), -(ROWNUM - 1)), 'yyyy-mm') ASC
(0)

相关推荐