Oracle递归查询
有的情况下，我们需要用递归的方法整理数据，这在程序中很容易做到，但是在数据库中，用SQL语句怎么实现？下面我以最典型的树形结构来说明下如何在Oracle使用递归查询。为了说明方便，创建一张数据库表，用于存储一个简单的树形结构create table TEST_TREE
(
ID NUMBER,
PIDNUMBER,
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
页:
[1]