--SQL: WITH T AS( SELECT 'A' a,'B' b FROM dual UNION ALL SELECT 'B','C' FROM dual UNION ALL SELECT 'A','D' FROM dual UNION ALL SELECT 'D','F' FROM dual UNION ALL SELECT 'B','E' FROM dual )SELECT DISTINCT a1.a,nvl(rn,a3.rm) FROM ( SELECT a FROM t UNION SELECT b FROM t ) a1, (SELECT a,LEVEL rn FROM t START WITH a='A' CONNECT BY a=PRIOR b) a2, (SELECT max(LEVEL)+1 rm FROM t START WITH a='A' CONNECT BY a=PRIOR b) a3 WHERE a1.a=a2.a(+)--result:A 1 B 2 C 3 D 2 E 3 F 3
仅供参考 with t as ( select 'a' parent,'b' child from dual union all select 'b' parent,'c' child from dual union all select 'a' parent,'d' child from dual union all select 'd' parent,'f' child from dual union all select 'b' parent,'e' child from dual ) select distinct t.parent,level from t start with t.parent='a' connect by prior child=parent;
WITH a AS (SELECT LEVEL lev,parent_id,child_id FROM lev START WITH parent_id='A' CONNECT BY parent_id=PRIOR child_id) SELECT lev,parent_id ID FROM a UNION SELECT lev+1 lev,child_id ID FROM a ORDER BY ID
是指表中所有LEVEL都是1的,都需要查询出来。不仅仅是A
如果我不指定start with,想查找出所有的level,怎么写呢?
这个问题就是Oracle的层次化查询嘛
with tmp as ( select PARENT,CHILD,LEVEL LV,LEVEL+1 LV1 from ( select 'A' PARENT,'B' CHILD from dual union all select 'B' PARENT,'C' CHILD from dual union all select 'A' PARENT,'D' CHILD from dual union all select 'D' PARENT,'F' CHILD from dual union all select 'B' PARENT,'E' CHILD from dual ) a START WITH PARENT = 'A' connect by PARENT = PRIOR CHILD ) SELECT PARENT,LV FROM TMP UNION SELECT CHILD,LV1 FROM TMP
--SQL:
WITH T AS(
SELECT
'A' a,'B' b FROM dual
UNION ALL
SELECT
'B','C' FROM dual
UNION ALL
SELECT
'A','D' FROM dual
UNION ALL
SELECT
'D','F' FROM dual
UNION ALL
SELECT
'B','E' FROM dual
)SELECT DISTINCT a1.a,nvl(rn,a3.rm) FROM
(
SELECT a FROM t
UNION
SELECT b FROM t
) a1,
(SELECT a,LEVEL rn FROM t
START WITH a='A'
CONNECT BY a=PRIOR b) a2,
(SELECT max(LEVEL)+1 rm FROM t
START WITH a='A'
CONNECT BY a=PRIOR b) a3
WHERE a1.a=a2.a(+)--result:A 1
B 2
C 3
D 2
E 3
F 3
with t as (
select 'a' parent,'b' child from dual union all
select 'b' parent,'c' child from dual union all
select 'a' parent,'d' child from dual union all
select 'd' parent,'f' child from dual union all
select 'b' parent,'e' child from dual
)
select distinct t.parent,level from t
start with t.parent='a'
connect by prior child=parent;
SELECT lev,parent_id ID FROM a
UNION
SELECT lev+1 lev,child_id ID FROM a
ORDER BY ID
(
select PARENT,CHILD,LEVEL LV,LEVEL+1 LV1 from (
select 'A' PARENT,'B' CHILD from dual
union all
select 'B' PARENT,'C' CHILD from dual
union all
select 'A' PARENT,'D' CHILD from dual
union all
select 'D' PARENT,'F' CHILD from dual
union all
select 'B' PARENT,'E' CHILD from dual
) a
START WITH PARENT = 'A'
connect by PARENT = PRIOR CHILD
)
SELECT PARENT,LV FROM TMP
UNION
SELECT CHILD,LV1 FROM TMP