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

 

转载于:https://www.cnblogs.com/zhuwenxia/p/9505545.html