CONNECT BY 用法及 row_number() over用法

1. 取某个时间段之间的日期列表
select a.DateRange, row_number() over(order by DateRange asc) dd 
from (
 SELECT to_date('2009-04-01', 'yyyy-mm-dd') - 1 + LEVEL AS DateRange
FROM dual
CONNECT BY LEVEL <= (to_date('2009-04-07', 'yyyy-mm-dd') - to_date('2009-04-01', 'yyyy-mm-dd') + 1)
 )a
结果：
DateRange   dd
 -------------- -   ---
2009-04-01    1
2009-04-02    2
2009-04-03    3
2009-04-04    4
2009-04-05    5
2009-04-06    6
2009-04-07    7

 2.按日期的各种排序
 select a.DateRange,
        row_number() over(order by DateRange asc) dd1,
        row_number() over(partition by DateRange order by DateRange asc) dd2 ,
        rank() over(order by DateRange asc) dd3,
        dense_rank() over(order by DateRange asc) dd4     
from (
 SELECT to_date('2009-04-01', 'yyyy-mm-dd') - 1 + LEVEL AS DateRange
FROM dual
CONNECT BY LEVEL <= (to_date('2009-04-07', 'yyyy-mm-dd') - to_date('2009-04-01', 'yyyy-mm-dd') + 1)
union all
 SELECT to_date('2009-04-01', 'yyyy-mm-dd') - 1 + LEVEL AS DateRange
FROM dual
CONNECT BY LEVEL <= (to_date('2009-04-07', 'yyyy-mm-dd') - to_date('2009-04-01', 'yyyy-mm-dd') + 1)
 )a
结果：
DateRange       dd1    dd2     dd3     dd4
----------     ----    ----    ----    ----
2009-04-01 1 1 1 1
2009-04-01 2 2 1 1
2009-04-02 3 1 3 2
2009-04-02 4 2 3 2
2009-04-03 5 1 5 3
2009-04-03 6 2 5 3
2009-04-04 7 1 7 4
2009-04-04 8 2 7 4
2009-04-05 9 1 9 5
2009-04-05 10 2 9 5
2009-04-06 11 1 11 6
2009-04-06 12 2 11 6
2009-04-07 13 1 13 7
2009-04-07 14 2 13 7

3. 周列表的行类型(创建人：xingtuyang)
create or replace TYPE TYPE_WEEKLIST_R
AS
  OBJECT
  (
    YR CHAR(4),
    WK CHAR(2),
    BG CHAR(20),
    ED CHAR(20));

4. 周列表的表类型(创建人：xingtuyang)
create or replace TYPE TYPE_WEEKLIST_T
AS
TABLE OF TYPE_WEEKLIST_R;

5. 周列表的函数，参数为起止日期，从每周五到下周四(创建人：xingtuyang)
create or replace FUNCTION FUNC_WEEKLIST_FRI
    (
      v_ks IN DATE ,
      v_js IN DATE )
    RETURN TYPE_WEEKLIST_T PIPELINED
  IS
  v_yr CHAR(4);
  v_wk CHAR(2);
  v_bg CHAR(20);
  v_ed CHAR(20);
  curs SYS_REFCURSOR;
BEGIN
    OPEN curs FOR
    SELECT TO_CHAR(v_ks,'yyyy') YR,
    TO_CHAR(v_ks+3 + LEVEL * 7 - 7,'IW') WK,
    TO_CHAR(NEXT_DAY(v_ks + 2 + LEVEL * 7 - 7,1)-9,'yyyy-mm-dd') BG,
    TO_CHAR(NEXT_DAY(v_ks + 2 + LEVEL * 7 - 7,1)-3,'yyyy-mm-dd') ED
    FROM dual
    CONNECT BY LEVEL <= TO_CHAR(v_js + 3,'IW') - TO_CHAR(v_ks + 3,'IW') + 1;
   
    LOOP
    FETCH curs INTO v_yr,v_wk,v_bg,v_ed;
    EXIT WHEN curs%NOTFOUND;
    PIPE ROW(TYPE_WEEKLIST_R(v_yr,v_wk,v_bg,v_ed));
    END LOOP;
    CLOSE curs;
END FUNC_WEEKLIST_FRI;

使用方式
 SELECT
  YR 年份   ,
  WK 周次   ,
  BG 起   ,
  ED 止
   FROM TABLE(FUNC_WEEKLIST_FRI(to_date('2009-01-01','yyyy-mm-dd'),to_date('2009-02-01','yyyy-mm-dd')))

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9252210/viewspace-592394/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9252210/viewspace-592394/