有关数据库SQL递归查询在不同数据库中的实现方法

本文给大家介绍有关数据库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

这样递归查询就很方便了。