# 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')))

• 博文量
400
• 访问量
1136022