一: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