请教各位帮忙写一个SQL语句,实现从下表的数据
表A
child father
001 0
002 001
004 002
005 002
006 002
003 001得出下面的结果:
LEVEL child father
1 001 0
2 002 001
3 004 002
3 005 002
3 006 002
2 003 001在此先表示感谢!!!
表A
child father
001 0
002 001
004 002
005 002
006 002
003 001得出下面的结果:
LEVEL child father
1 001 0
2 002 001
3 004 002
3 005 002
3 006 002
2 003 001在此先表示感谢!!!
from a
connect by prior child=father
WITH a AS
(SELECT '001' CHILD, '0' father
FROM DUAL
UNION ALL
SELECT '002', '001'
FROM DUAL
UNION ALL
SELECT '004', '002'
FROM DUAL
UNION ALL
SELECT '005', '002'
FROM DUAL
UNION ALL
SELECT '006', '002'
FROM DUAL
UNION ALL
SELECT '003', '001'
FROM DUAL)SELECT LEVEL lv, CHILD, father
FROM a
START WITH CHILD = '001'
CONNECT BY PRIOR CHILD = father
结果
Row# LV CHILD FATHER1 1 001 0
2 2 002 001
3 3 004 002
4 3 005 002
5 3 006 002
6 2 003 001
会一级级列出来,还是要加上start with
用楼上语句就行了
2 union
3 select '002' child,'001' father from dual
4 union
5 select '004' child,'002' father from dual
6 union
7 select '005' child,'002' father from dual
8 union
9 select '006' child,'002' father from dual
10 union
11 select '003' child,'001' father from dual
12 )
13 select LEVEL,CHILD,FATHER
14 FROM A
15 START WITH FATHER='0'
16 CONNECT BY FATHER=PRIOR CHILD
17 /
LEVEL CHILD FATHER
---------- ----- ------
1 001 0
2 002 001
3 004 002
3 005 002
3 006 002
2 003 001
6 rows selected