有关数据库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

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

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐