递归查询树形结构的SQL

一.在Oracle中可使用Start with...Connect By子句

Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。

创建示例表：

CREATE TABLE TBL_TEST ( ID NUMBER, NAME VARCHAR2(100 BYTE), PID NUMBER DEFAULT 0 );

 

插入测试数据：

INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0'); INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1'); INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0'); INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1'); INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');

 

从Root往树末梢递归

select * from TBL_TEST start with id=1 connect by prior id = pid

 

从末梢往树ROOT递归

select * from TBL_TEST start with id=5 connect by prior pid = id
二.写一个函数，调用函数查询

--测试数据
CREATETABLE tb(ID char(3),PID char(3),Name nvarchar(10)) 
INSERT tb SELECT'001',NULL ,'山东省'
UNIONALLSELECT'002','001','烟台市'
UNIONALLSELECT'004','002','招远市'
UNIONALLSELECT'003','001','青岛市'
UNIONALLSELECT'005',NULL ,'四会市'
UNIONALLSELECT'006','005','清远市'
UNIONALLSELECT'007','006','小分市'
GO
--查询指定节点及其所有子节点的函数
CREATEFUNCTION f_Cid(@IDchar(3)) 
RETURNS@t_LevelTABLE(ID char(3),Levelint) 
AS
BEGIN
DECLARE@Levelint
SET@Level=1
INSERT@t_LevelSELECT@ID,@LevelWHILE@@ROWCOUNT>0
BEGIN
SET@Level=@Level+1INSERT@t_LevelSELECT a.ID,@LevelFROM tb a,@t_Level b  WHERE a.PID=b.ID  AND b.Level=@Level-1ENDRETURNENDGO
--调用函数查询002及其所有子节点
SELECT a.*FROM tb a,f_Cid('002') b WHERE a.ID=b.ID