mysql+orcle(sql实现递归查询子节点所有数据)

一：MySQL（本人亲测）
话不多说直接上代码：
1.创建表格

CREATE TABLE `treenodes` (
  `id` int , -- 节点ID
  `nodename` varchar (60), -- 节点名称
  `pid` int -- 节点父ID
); 

2.插入测试数据

INSERT INTO `treenodes` (`id`, `nodename`, `pid`) VALUES
('1','A','0'),('2','B','1'),('3','C','1'),
('4','D','2'),('5','E','2'),('6','F','3'),
('7','G','6'),('8','H','0'),('9','I','8'),
('10','J','8'),('11','K','8'),('12','L','9'),
('13','M','9'),('14','N','12'),('15','O','12'),
('16','P','15'),('17','Q','15'),('18','R','3'),
('19','S','2'),('20','T','6'),('21','U','8');

3.查询语句

SELECT 
    * 
 FROM 
    (SELECT id, nodename, pid FROM treenodes WHERE pid IS NOT NULL) rd,
    (SELECT @pid := 1) pd
 where FIND_IN_SET( pid, @pid ) > 0 AND @pid := concat( @pid, ',', id );

4.结果

二：Oracle
1.创建表

CREATE TABLE DISTRICT
(
  ID         NUMBER(10)                  NOT NULL,
  PARENT_ID  NUMBER(10),
  NAME       VARCHAR2(255 BYTE)          NOT NULL
);

ALTER TABLE DISTRICT ADD (
  CONSTRAINT DISTRICT_PK
 PRIMARY KEY
 (ID));

ALTER TABLE DISTRICT ADD (
  CONSTRAINT DISTRICT_R01 
 FOREIGN KEY (PARENT_ID) 
 REFERENCES DISTRICT (ID));

2.插入数据

insert into DISTRICT (id, parent_id, name)
values (1, null, '四川省');
insert into DISTRICT (id, parent_id, name)
values (2, 1, '巴中市');
insert into DISTRICT (id, parent_id, name)
values (3, 1, '达州市');
insert into DISTRICT (id, parent_id, name)
values (4, 2, '巴州区');
insert into DISTRICT (id, parent_id, name)
values (5, 2, '通江县');
insert into DISTRICT (id, parent_id, name)
values (6, 2, '平昌县');
insert into DISTRICT (id, parent_id, name)
values (7, 3, '通川区');
insert into DISTRICT (id, parent_id, name)
values (8, 3, '宣汉县');
insert into DISTRICT (id, parent_id, name)
values (9, 8, '塔河乡');
insert into DISTRICT (id, parent_id, name)
values (10, 8, '三河乡');
insert into DISTRICT (id, parent_id, name)
values (11, 8, '胡家镇');
insert into DISTRICT (id, parent_id, name)
values (12, 8, '南坝镇');
insert into DISTRICT (id, parent_id, name)
values (13, 6, '大寨乡');
insert into DISTRICT (id, parent_id, name)
values (14, 6, '响滩镇');
insert into DISTRICT (id, parent_id, name)
values (15, 6, '龙岗镇');
insert into DISTRICT (id, parent_id, name)
values (16, 6, '白衣镇');
commit;

3.start with connect by prior递归

SELECT *
FROM district
START WITH NAME ='巴中市'
CONNECT BY PRIOR ID=parent_id