干货Oracle、SqlServer、MySQL树的递归

一、Oracle

Oracle中的select语句可以用start with…connect by prior子句实现递归查询，connect by 是结构化查询中用到的，其基本语法是：

select … from

where

start with

connect by ;

：过滤条件，用于对返回的所有记录进行过滤。

：查询结果重起始根结点的限定条件。

：连接条件

数据库表结构如下：

create table t2(

root_id number,

id number,

name varchar(5),

description varchar(10)

);

insert into t2(root_id,id,name,description) values(0,1,’a’,’aaa’);

insert into t2(root_id,id,name,description) values(1,2,’a1’,’aaa1’);

insert into t2(root_id,id,name,description) values(1,3,’a2’,’aaa2’);

insert into t2(root_id,id,name,description) values(0,4,’b’,’bbb’);

insert into t2(root_id,id,name,description) values(4,5,’b1’,’bbb1’);

insert into t2(root_id,id,name,description) values(4,6,’b2’,’bbb2’);

获取完整树：

select \ from t2 start with root_id = 0 connect by prior id = root_id;*

获取特定子树：

select * from t2 start with id = 1 connect by prior id = root_id;

select * from t2 start with id = 4 connect by prior id = root_id;

如果connect by prior中的prior被省略，则查询将不进行深层递归。

select * from t2 start with root_id = 0 connect by id = root_id;

select * from t2 start with id = 1 connect by id = root_id;

二、SqlServer

比如一个表，有id和pId字段，id是主键，pid表示它的上级节点，表结构和数据：

CREATE TABLE [aaa](

[id] [int] NULL,

[pid] [int] NULL,

[name] [nchar](10)

)

GO

INSERT INTO aaa VALUES(1,0,’a’)

INSERT INTO aaa VALUES(2,0,’b’)

INSERT INTO aaa VALUES(3,1,’c’)

INSERT INTO aaa VALUES(4,1,’d’)

INSERT INTO aaa VALUES(5,2,’e’)

INSERT INTO aaa VALUES(6,3,’f’)

INSERT INTO aaa VALUES(7,3,’g’)

INSERT INTO aaa VALUES(8,4,’h’)

GO

--下面的Sql是查询出1结点的所有子结点

with my1 as(select \ from aaa where id = 1*

• union all select aaa.* from my1, aaa where my1.id = aaa.pid*

)

select * from my1 —结果包含1这条记录，如果不想包含，可以在最后加上:where id <> 1

--下面的Sql是查询出8结点的所有父结点

with my1 as(select * from aaa where id = 8

union all select aaa.* from my1, aaa where my1.pid = aaa.id

)

select * from my1;

--下面是递归删除1结点和所有子结点的语句：

with my1 as(select * from aaa where id = 1

union all select aaa.* from my1, aaa where my1.id = aaa.pid

)

delete from aaa where exists (select id from my1 where my1.id = aaa.id)

注：oracle中也可以用with结构实现递归查询

三、MySql​

1.利用函数​

​— 创建表

CREATE TABLE category(

id INT PRIMARY KEY,

p_id INT NOT NULL,

name VARCHAR(20) NOT NULL,

price DOUBLE

)

-- 插入数据

INSERT INTO `category` VALUES (‘1’, ‘0’, ‘男装’, ‘1.15’);

INSERT INTO `category` VALUES (‘2’, ‘0’, ‘女装’, ‘2.5’);

INSERT INTO `category` VALUES (‘3’, ‘1’, ‘运动’, ‘11.5’);

INSERT INTO `category` VALUES (‘4’, ‘1’, ‘休闲’, ‘11.6’);

INSERT INTO `category` VALUES (‘5’, ‘1’, ‘正装’, ‘11.7’);

INSERT INTO `category` VALUES (‘6’, ‘2’, ‘正装’, ‘11.1’);

INSERT INTO `category` VALUES (‘7’, ‘2’, ‘运动’, ‘11.23’);

INSERT INTO `category` VALUES (‘8’, ‘2’, ‘休闲’, ‘11.34’);

INSERT INTO `category` VALUES (‘9’, ‘3’, ‘春装’, ‘11.7’);

INSERT INTO `category` VALUES (‘10’, ‘3’, ‘夏装’, ‘11.7’);

INSERT INTO `category` VALUES (‘11’, ‘3’, ‘冬装’, ‘11.7’);

INSERT INTO `category` VALUES (‘12’, ‘7’, ‘春装’, ‘11.7’);

INSERT INTO `category` VALUES (‘13’, ‘7’, ‘夏装’, ‘11.7’);

INSERT INTO `category` VALUES (‘14’, ‘7’, ‘秋装’, ‘11.7’);

INSERT INTO `category` VALUES (‘15’, ‘8’, ‘衬衫’, ‘55.2’);

INSERT INTO `category` VALUES (‘16’, ‘8’, ‘裤子’, ‘78’);

-- 查询表数据

select * from category;

-- 创建函数，参数是父类ID，查询该父类下面所有的子孙

CREATE FUNCTION getChildList(rootId INT)

RETURNS VARCHAR(1000)

BEGIN

DECLARE cTemp,cTempChild VARCHAR(1000);

SET cTemp = ‘$’;

SET cTempChild = CAST(rootId AS CHAR);

WHILE cTempChild IS NOT NULL DO

SET cTemp = CONCAT(cTemp,’,’,cTempChild);

SELECT GROUP_CONCAT(id) INTO cTempChild FROM category WHERE FIND_IN_SET(p_id,cTempChild)>0;

END WHILE;

RETURN cTemp;

END

-- 测试

SELECT * fromcategory WHERE FIND_IN_SET(id,getChildList(1));

-- 这样可以实现递归查询

-- 优点:简单方便，没有递归调用层次深度的限制 (max_sp_recursion_depth,最大255)

-- 缺点：长度受限，虽然可以扩大 RETURNS varchar(1000)，但总是有最大限制的。

2.​利用临时表和过程递归

​— 创建存储过程如下。createChildLst 为递归过程，showChildLst为调用入口过程，准备临时表及初始化。

-- 利用临时表和过程递归

-- 创建存储过程 入口过程

CREATE PROCEDURE showChildList(in rootId INT)

BEGIN

CREATE TEMPORARY TABLE IF NOT EXISTS tmpList(sno INT PRIMARY KEY auto_increment,id INT,depth INT);

DELETE FROM tmpList;

CALL createChildList(rootId,0);

SELECT tmpList.*,category.* FROM tmpList,category WHERE tmpList.id = category.id ORDER BY tmpList.sno;

END;

-- 递归过程

CREATE PROCEDURE createChildList(IN rootId INT,IN nDepth INT)

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE b INT ;

DECLARE cur1 CURSOR FOR SELECT id FROM category where p_id=rootId;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

INSERT INTO tmpList VALUES(null,rootId,nDepth);

OPEN cur1;

FETCH cur1 INTO b;

WHILE done=0 DO

CALL createChildList(b,nDepth+1);

FETCH cur1 INTO b;

END WHILE;

CLOSE cur1;

END;

-- 说明

-- depth 为深度，这样可以在程序进行一些显示上的格式化处理。类似于oracle中的 level 伪列。sno 仅供排序控制。这样你还可以通过临时表tmpLst与数据库中其它表进行联接查询。

-- 设置递归层数限制

SET max_sp_recursion_depth = 4; — 这里要先设置递归层数，不然查询不到数据，因为设置递归层数为0

-- 传入ID查询数据

CALL showChildList(1);

-- 优点 : 可以更灵活处理，及层数的显示。并且可以按照树的遍历顺序得到结果。

-- 缺点 : 递归有255的限制。

​3.利用中间表和过程

-- 创建存储过程如下。由于MySQL中不允许在同一语句中对临时表多次引用，只以使用普通表tmpLst来实现了。

-- 当然你的程序中负责在用完后清除这个表。

drop PROCEDURE IF EXISTS showCategory;— 如果存在这个过程就先删除

-- 创建存储过程

CREATE PROCEDURE showCategory(IN rootID INT)

BEGIN

DECLARE Level int ;

drop TABLE IF EXISTS tmpLst;

CREATE TABLE tmpLst (

id int,

nLevel int,

sCort varchar(8000)

);

Set Level=0 ;

INSERT into tmpLst SELECT id,Level,ID FROM category WHERE p_id=rootid;

WHILE ROW_COUNT()>0 DO

SET Level=Level+1 ;

INSERT into tmpLst

SELECT A.ID,Level,concat(B.sCort,A.ID) FROM category A,tmpLst B

1. WHERE A.p\_id=B.ID AND B.nLevel=Level-1 ;

END WHILE;

END;

CALL showTreeNodes_yongyupost2000(0);— 调用存储过程

-- 执行完后会产生一个tmpLst表，nLevel 为节点深度，sCort 为排序字段。

-- 使用方法:

SELECT concat(SPACE(B.nLevel*2),’+—‘,A.name)

FROM category A,tmpLst B

WHERE A.id=B.id

ORDER BY B.sCort;

-- 优点 : 层数的显示。并且可以按照树的遍历顺序得到结果。没有递归限制。

-- 缺点 : MySQL中对临时表的限制，只能使用普通表，需做事后清理。