递归查询树形结构的SQL

时间:2022-05-08 00:26:06

一.在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','清远市'
UNION
ALLSELECT'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