1.概述
Oracle “CONNECT BY”是层次查询子句,一般用于树状或者层次结果集的查询
2.使用方式
2.1.通过Connect by 生成序列
Oracle 构造一个月份的天数 select to_date('','yyyymm')+(rownum-1) s_date from dual
connect by
rownum<=last_day(to_date('','yyyymm')) - to_date('','yyyymm') + 1
生成1-10的一个序列 SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 10
2.2实现树状查询结果
create table DEP
(
DEPID number(10) not null,
DEPNAME varchar2(256),
UPPERDEPID number(10)
)
---------------------------------------------------------------------
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (0, '总经办', null);
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (1, '开发部', 0);
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (2, '测试部', 0);
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (3, 'Sever开发部', 1);
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (4, 'Client开发部', 1);
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (5, 'TA测试部', 2);
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (6, '项目测试部', 2);
--------------------------------------------------------------------- SELECT RPAD( ' ', 2*(LEVEL-1), '-' ) || DEPNAME "DEPNAME",
CONNECT_BY_ROOT DEPNAME "ROOT",
CONNECT_BY_ISLEAF "ISLEAF",
LEVEL ,
SYS_CONNECT_BY_PATH(DEPNAME, '/') "PATH"
FROM DEP
START WITH UPPERDEPID IS NULL
CONNECT BY PRIOR DEPID = UPPERDEPID;