本文给大家介绍有关数据库sql递归查询在不同数据库中的实现方法,具体内容请看下文。
比如表结构数据如下:
table:tree
id name parentid
1 一级 0
2 二级 1
3 三级 2
4 四级 3
sql server 2005查询方法:
//上查 with tmptree as ( select * from tree where id=2 union all select p.* from tmptree inner join tree p on p.id=tmptree.parentid ) select * from tmptree //下查 with tmptree as ( select * from tree where id=2 union all select s.* from tmptree inner join tree s on s.parentid=tmptree.id ) select * from tmptree
sql server 2008及以后版本,还可用如下方法:
增加一列tid,类型设为:hierarchyid(这个是clr类型,表示层级),且取消parentid字段,变成如下:(表名为:tree2)
tid id name
0x 1 一级
0x58 2 二级
0x5b40 3 三级
0x5b5e 4 四级
查询方法:
select *,tid.getlevel() as [level] from tree2 --获取所有层级 declare @parenttree hierarchyid select @parenttree=tid from tree2 where id=2 select *,tid.getlevel()as [level] from tree2 where tid.isdescendantof(@parenttree)=1 --获取指定的节点所有下级 declare @childtree hierarchyid select @childtree=tid from tree2 where id=3 select *,tid.getlevel()as [level] from tree2 where @childtree.isdescendantof(tid)=1 --获取指定的节点所有上级
oracle中的查询方法:
select * from tree start with id=2 connect by prior id=parentid --下查 select * from tree start with id=2 connect by id= prior parentid --上查
mysql 中的查询方法:
//定义一个依据id查询所有父id为这个指定的id的字符串列表,以逗号分隔 create definer=`root`@`localhost` function `getchildlst`(rootid int,direction int) returns varchar(1000) charset utf8 begin declare stemp varchar(5000); declare stempchd varchar(1000); set stemp = '$'; if direction=1 then set stempchd =cast(rootid as char); elseif direction=2 then select cast(parentid as char) into stempchd from tree where id=rootid; end if; while stempchd is not null do set stemp = concat(stemp,',',stempchd); select group_concat(id) into stempchd from tree where (direction=1 and find_in_set(parentid,stempchd)>0) or (direction=2 and find_in_set(id,stempchd)>0); end while; return stemp; end //查询方法: select * from tree where find_in_set(id,getchildlst(1,1));--下查 select * from tree where find_in_set(id,getchildlst(1,2));--上查
补充说明:上面这个方法在下查是没有问题,但在上查时会出现问题,原因在于我的逻辑写错了,存在死循环,现已修正,新的方法如下:
create definer=`root`@`localhost` function `getchildlst`(rootid int,direction int) returns varchar(1000) charset utf8 begin declare stemp varchar(5000); declare stempchd varchar(1000); set stemp = '$'; set stempchd =cast(rootid as char); if direction=1 then while stempchd is not null do set stemp = concat(stemp,',',stempchd); select group_concat(id) into stempchd from tree where find_in_set(parentid,stempchd)>0; end while; elseif direction=2 then while stempchd is not null do set stemp = concat(stemp,',',stempchd); select group_concat(parentid) into stempchd from tree where find_in_set(id,stempchd)>0; end while; end if; return stemp; end
这样递归查询就很方便了。