create table cp(u int,uname int,parentid int);
测试数据:
select rownum+1,rownum,rownum from dual connect by rownum<=10000;
传统方式:
select * from (
SELECT
LEVEL as lvl,
u,parentid
FROM cp t1
start with parentid IS NULL
connect by parentid= prior u
) h where (0= 0 OR .lvl = 0);
Oracle11g新的方式:
WITH T(u,
uname,
parentid,
the_level,
path) AS
( ---- 必须把结构写出来
SELECT u,
uname,
parentid ---- 先写锚点查询,用START WITH的条件
,
1 AS the_level ---- 递归起点,第一层
,
'\' || uname ---- 路径的第一截
FROM cp
WHERE parentid IS NULL ---- 原来的START WITH条件
UNION ALL ---- 下面是递归部分
SELECT e.u,
e.uname,
e.parentid ---- 要加入的新一层数据,来自要遍历的emp表
,
1 + t.the_level ---- 递归层次,在原来的基础上加1
,
t.path || '\' || e.uname ---- 把新的一截路径拼上去
FROM t, cp e ---- 典型写法,把子查询本身和要遍历的表作一个连接
WHERE t.u = e.parentid ---- 原来的CONNECT BY条件
) SEARCH DEPTH FIRST BY parentid SET seq --- 指定DEPTH深度优先 BREADTH广度优先, 按顺序生成序号seq
SELECT * FROM T
ORDER BY seq;