子树查的多,现在遇到父亲树,谁曾经也遇到这样的树?

[lovepapa]

A
/ \
B C
/ \ / \
D E F G
\
H
数据表是这样的:parentTabel
id, name ,parentId
--------------------
1 A
2 B 1
3 C 1
4 D 2
5 E 2
6 F 3
7 G 3
8 H 7

我要找出 D 和 H 的父亲树,
我用的是:
select distinct id,Name,parentId,level from parenttable
start with id in (4,8) connect by prior parentId = id
查出来会有两个A,一个等级是level =3,一个是 level = 4
我也知道因为是以子节点开始的,查出来的父节点的level是不同的,

谁知道怎样把 D 的父亲和 H的父亲树合并起来??最后查处一棵这样的树:
A
/ \
B C
/ \
D G
\
H

[lovepapa]

搞了很久,搞不出来,现在头都大了!!

[lovepapa]

把表结构列出来,好让大家调试使用:

CREATE TABLE PARENTTABLE
(
ID NUMBER,
NAME VARCHAR2(200),
PARENTID NUMBER
);
INSERT INTO PARENTTABLE ( ID, NAME, PARENTID ) VALUES ( 1, 'A', NULL);
INSERT INTO PARENTTABLE ( ID, NAME, PARENTID ) VALUES ( 2, 'B', 1);
INSERT INTO PARENTTABLE ( ID, NAME, PARENTID ) VALUES ( 3, 'C', 1);
INSERT INTO PARENTTABLE ( ID, NAME, PARENTID ) VALUES ( 4, 'D', 2);
INSERT INTO PARENTTABLE ( ID, NAME, PARENTID ) VALUES ( 5, 'E', 2);
INSERT INTO PARENTTABLE ( ID, NAME, PARENTID ) VALUES ( 6, 'F', 3);
INSERT INTO PARENTTABLE ( ID, NAME, PARENTID ) VALUES ( 7, 'G', 3);
INSERT INTO PARENTTABLE ( ID, NAME, PARENTID ) VALUES ( 8, 'H', 7);
COMMIT;

[Anders_xue]

select distinct id,Name,parentId,level from parenttable
start with id in (4,8) connect by prior id=parentId
试一下看看

[beckhambobo]

http://community.csdn.net/Expert/FAQ/FAQ_Index.asp?id=170559

[北极海hein]

以下代码测试通过.
建议你把
select distinct id,Name,parentId,LEVEL AS lev from parenttable
start with id in (4,8) connect by prior parentId = id
做成一 View 增强可读性.
--==============================

SELECT * FROM
( select distinct id,Name,parentId,LEVEL AS lev from parenttable
start with id in (4,8) connect by prior parentId = id
)
WHERE lev =

(SELECT MAX(a.lev) maxlev FROM
(
select distinct id,Name,parentId,LEVEL AS lev from parenttable
start with id in (4,8) connect by prior parentId = id
) a
WHERE a.parentid IS NULL
GROUP BY id
)
UNION
SELECT * FROM
(
select distinct id,Name,parentId,LEVEL AS lev from parenttable
start with id in (4,8) connect by prior parentId = id
) b
WHERE b.parentid IS NOT NULL

[北极海hein]

Anders_xue(小K):
你这是由父亲找儿子.

[mycore]

不知道,帮你顶

[ks9960]

有什么用吗？