欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-11-18 19:46:58
本文给大家介绍有关数据库sql递归查询在不同数据库中的实现方法,具体内容请看下文。 比如表结构数据如下: table:tree id name parentid 1...

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

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