Oracle的递归公共表表达式

查询节点id为2的所有子节点的数据，包括向下级联

WITH T1 (id, parent_id, data) AS (SELECT id, parent_id, dataFROM nodesWHERE id = 2UNION ALLSELECT t.id, t.parent_id, t.dataFROM nodes tJOIN T1 n ON t.parent_id = n.id
)
SELECT * FROM T1;

--建表语句

CREATE TABLE nodes (id NUMBER,parent_id NUMBER,data varchar2(100)
);

--插入数据

INSERT INTO nodes (id, parent_id, data) VALUES (1, NULL, 'Node 1');
INSERT INTO nodes (id, parent_id, data) VALUES (2, 1, 'Node 2');
INSERT INTO nodes (id, parent_id, data) VALUES (3, 1, 'Node 3');
INSERT INTO nodes (id, parent_id, data) VALUES (4, 2, 'Node 4');
INSERT INTO nodes (id, parent_id, data) VALUES (5, 2, 'Node 5');
INSERT INTO nodes (id, parent_id, data) VALUES (6, 3, 'Node 6');
INSERT INTO nodes (id, parent_id, data) VALUES (7, 3, 'Node 7');
INSERT INTO nodes (id, parent_id, data) VALUES (8, 4, 'Node 8');
INSERT INTO nodes (id, parent_id, data) VALUES (9, 4, 'Node 9');
INSERT INTO nodes (id, parent_id, data) VALUES (10, 5, 'Node 10');
INSERT INTO nodes (id, parent_id, data) VALUES (11, 5, 'Node 11');
INSERT INTO nodes (id, parent_id, data) VALUES (12, 6, 'Node 12');
INSERT INTO nodes (id, parent_id, data) VALUES (13, 6, 'Node 13');
INSERT INTO nodes (id, parent_id, data) VALUES (14, 7, 'Node 14');
INSERT INTO nodes (id, parent_id, data) VALUES (15, 7, 'Node 15');

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.xdnf.cn/news/145847.html

如若内容造成侵权/违法违规/事实不符，请联系一条长河网进行投诉反馈，一经查实，立即删除！