使用connect by產生時間列表的方法

1. 產生年份列表
   select 1949 + rownum - 1 as year
     from dual
   connect by 1949 + rownum - 1 <= to_char(sysdate, 'yyyy')
    order by year desc
2. 產生月份列表
   select to_char(add_months(to_date('2000-01','yyyy-mm'), rownum - 1), 'yyyymm') as month
     from dual
   connect by add_months(to_date('2000-01','yyyy-mm'), rownum - 1) <= trunc(sysdate, 'mm')
    order by month desc
3. 產生天列表
   select to_char(to_date('2007-12-01','yyyy-mm-dd') + rownum - 1, 'yyyymmdd') as day
     from dual
   connect by to_date('2007-12-01','yyyy-mm-dd') + rownum - 1 <= trunc(sysdate)
    order by day desc
4. 產生小時列表
   select to_char(to_date('2007-12-27','yyyy-mm-dd') + (rownum - 1) / 24, 'yyyymmddhh24') as hour
     from dual
   connect by to_date('2007-12-27','yyyy-mm-dd') + (rownum - 1) / 24 <=
              trunc(sysdate,'hh24')
    order by hour desc