有的情况下,我们需要用递归的方法整理数据,这在程序中很容易做到,但是在数据库中,用SQL语句怎么实现?下面我以最典型的树形结构来说明下如何在Oracle使用递归查询。为了说明方便,创建一张数据库表,用于存储一个简单的树形结构
create table TEST_TREE
(
ID NUMBER,
PID NUMBER,
NAME VARCHAR2(32)
)
insert into digui values(1,'总公司','0')
insert into digui values(2,'华东管理区','1');
insert into digui values(21,'华东管理区21','2');
insert into digui values(22,'华东管理区22','2');
insert into digui values(3,'华西管理区','1');
insert into digui values(31,'华西管理区','3');
insert into digui values(32,'华西管理区','3');
insert into digui values(33,'华西管理区','3');
insert into digui values(4,'华南管理区','1');
insert into digui values(41,'华南管理区','4');
insert into digui values(42,'华南管理区','4');
insert into digui values(43,'华南管理区','4');
insert into digui values(44,'华南管理区','4');
insert into digui values(5,'华北管理区','1');
insert into digui values(51,'华南管理区','5');
insert into digui values(52,'华南管理区','5');
insert into digui values(53,'华南管理区','5');
insert into digui values(54,'华南管理区','5');
insert into digui values(55,'华南管理区','5');
select SYS_CONNECT_BY_PATH(t1.NAME,'>>') AS NAME
from digui t1
start with id = '1'
connect by prior id = pid;
SELECT SYS_CONNECT_BY_PATH(T1.NAME,'>>') AS NAME
from YTO_DISTRICT T1
START WITH T1.DISTRICT_ID IN (SELECT DISTRICT_ID
FROM YTO_DISTRICT
WHERE PID='0')
CONNECT BY PRIOR T1.DISTRICT_ID=T1.PID
create table TEST_TREE
(
ID NUMBER,
PID NUMBER,
NAME VARCHAR2(32)
)
insert into digui values(1,'总公司','0')
insert into digui values(2,'华东管理区','1');
insert into digui values(21,'华东管理区21','2');
insert into digui values(22,'华东管理区22','2');
insert into digui values(3,'华西管理区','1');
insert into digui values(31,'华西管理区','3');
insert into digui values(32,'华西管理区','3');
insert into digui values(33,'华西管理区','3');
insert into digui values(4,'华南管理区','1');
insert into digui values(41,'华南管理区','4');
insert into digui values(42,'华南管理区','4');
insert into digui values(43,'华南管理区','4');
insert into digui values(44,'华南管理区','4');
insert into digui values(5,'华北管理区','1');
insert into digui values(51,'华南管理区','5');
insert into digui values(52,'华南管理区','5');
insert into digui values(53,'华南管理区','5');
insert into digui values(54,'华南管理区','5');
insert into digui values(55,'华南管理区','5');
select SYS_CONNECT_BY_PATH(t1.NAME,'>>') AS NAME
from digui t1
start with id = '1'
connect by prior id = pid;
SELECT SYS_CONNECT_BY_PATH(T1.NAME,'>>') AS NAME
from YTO_DISTRICT T1
START WITH T1.DISTRICT_ID IN (SELECT DISTRICT_ID
FROM YTO_DISTRICT
WHERE PID='0')
CONNECT BY PRIOR T1.DISTRICT_ID=T1.PID