把表结构列出来,好让大家调试使用: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;
(
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;
start with id in (4,8) connect by prior id=parentId
试一下看看
建议你把
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
你这是由父亲找儿子.