数据库中数据:
图:
现有要求如下:
知道一个节点,求该节点下所有没有子节点的子节点
如:
1--->6,7,8
2-->7,8
请问sql语句应该怎么写?
主要是分层查询,sql脚本如下:
CREATE TABLE TEST_A (ID NUMBER,PID NUMBER)
INSERT INTO TEST_A
VALUES(1,0);
COMMIT;
INSERT INTO TEST_A
VALUES(2,1);
COMMIT;
INSERT INTO TEST_A
VALUES(3,1);
COMMIT;
INSERT INTO TEST_A
VALUES(4,2);
COMMIT;
INSERT INTO TEST_A
VALUES(5,2);
COMMIT;
INSERT INTO TEST_A
VALUES(6,3);
COMMIT;
INSERT INTO TEST_A
VALUES(7,4);
COMMIT;
INSERT INTO TEST_A
VALUES(8,5);
COMMIT;
SELECT *
FROM TEST_A A
START WITH A.PID = 0
CONNECT BY PRIOR A.ID = A.PID
ORDER BY A.PID;
转自:http://zhidao.baidu.com/link?url=JG0B8vEntYqwHvmB6wdqiwk-843x5osIwzXJ_11c-EMRUIuv_364Ow4shNHjk7bgDKbtCfZjIJK478QU1x3S2jjqP6HMLurLkbuHq78_J8G