请教SQL表结构如:
Parent Child
A B
A D
A F
B T
B S
D H
F M
N B
N L
N Z
L D
L C
Z D
Z F如何写SQL语句分别得到任何一个parent下的存在的亲子关系。
例如:
A下面存在的亲子关系有:
A - B
A - D
A - F
B - T
B - S
D - H
F - M
如何写SQL语句得到任何一个parent下的所有节点,直至根节点
例如:
A的子孙有:
B\T\S\D\H\F\M如何写SQL语句得到任何一个child的所有祖先.
例如:
D的祖先有:
L/N/Z/A谢谢~~~
Parent Child
A B
A D
A F
B T
B S
D H
F M
N B
N L
N Z
L D
L C
Z D
Z F如何写SQL语句分别得到任何一个parent下的存在的亲子关系。
例如:
A下面存在的亲子关系有:
A - B
A - D
A - F
B - T
B - S
D - H
F - M
如何写SQL语句得到任何一个parent下的所有节点,直至根节点
例如:
A的子孙有:
B\T\S\D\H\F\M如何写SQL语句得到任何一个child的所有祖先.
例如:
D的祖先有:
L/N/Z/A谢谢~~~
select * from tablename
start with parent = 'A'
connect by prior parent = child
select * from tablename
start with child = 'F'
connect by prior child = parent
( select 'A' parent,'B' child from dual UNION
select 'A' parent,'D' child from dual UNION
select 'A' parent,'F' child from dual UNION
select 'B' parent,'T' child from dual UNION
select 'B' parent,'S' child from dual UNION
select 'D' parent,'H' child from dual UNION
select 'F' parent,'M' child from dual UNION
select 'N' parent,'B' child from dual UNION
select 'N' parent,'L' child from dual UNION
select 'N' parent,'Z' child from dual UNION
select 'L' parent,'D' child from dual UNION
select 'L' parent,'C' child from dual UNION
select 'Z' parent,'D' child from dual UNION
select 'Z' parent,'F' child from dual
)
select child from t
start with parent='A'
connect by prior child = parent;
( select 'A' parent,'B' child from dual UNION
select 'A' parent,'D' child from dual UNION
select 'A' parent,'F' child from dual UNION
select 'B' parent,'T' child from dual UNION
select 'B' parent,'S' child from dual UNION
select 'D' parent,'H' child from dual UNION
select 'F' parent,'M' child from dual UNION
select 'N' parent,'B' child from dual UNION
select 'N' parent,'L' child from dual UNION
select 'N' parent,'Z' child from dual UNION
select 'L' parent,'D' child from dual UNION
select 'L' parent,'C' child from dual UNION
select 'Z' parent,'D' child from dual UNION
select 'Z' parent,'F' child from dual
)
select distinct parent from t
start with child='D'
connect by child = prior parent;
D--L--N
D--Z--N
D--A
T--B--A
S--B--A
H--D--A
M--F--A
B--N
B--A
L--N
Z--N
C--L--N
F--Z--N
(
PARENT VARCHAR2(4),
CHILD VARCHAR2(4)
);INSERT INTO T35 VALUES('A', 'B');
INSERT INTO T35 VALUES('A', 'D');
INSERT INTO T35 VALUES('A', 'F');
INSERT INTO T35 VALUES('B', 'T');
INSERT INTO T35 VALUES('B', 'S');
INSERT INTO T35 VALUES('D', 'H');
INSERT INTO T35 VALUES('F', 'M');
INSERT INTO T35 VALUES('N', 'B');
INSERT INTO T35 VALUES('N', 'L');
INSERT INTO T35 VALUES('N', 'Z');
INSERT INTO T35 VALUES('L', 'D');
INSERT INTO T35 VALUES('L', 'C');
INSERT INTO T35 VALUES('Z', 'D');
INSERT INTO T35 VALUES('Z', 'F');
测试结果: