在ORACLE、MSSQL、MYSQL中树结构表递归查询的实现方法
表recursion数据如下:
id?????? name?????? parentid
1?????? 食品分类???? -1
2?????? 肉类????????????? 1
3?????? 蔬菜类????????? 1
4?????? 产品分类???? -1
5?????? 保健品????????? 4
6?????? 医药????????????? 4
7?????? 建筑????????????? 4
一 ORACLE中实现方法:
Oracle中直接支持,使用语句select * from tablename start with connect by
prior id(子层的列)=parentid(属于顶层的列)
语句说明:
start with 指定层次开始的条件，即满足这个条件的行即可以作为层次树的最顶层
connect by prior指层之间的关联条件，即什么样的行是上层行的子行（自连接条件）
实例:
select * from recursionstart with connect by prior >查询结果:
id?????? name?????? parentid
1?????? 食品分类???? -1
2?????? 肉类????????????? 1
3?????? 蔬菜类????????? 1
二 MSSQL中的实现方法
在MSSQL中需要使用临时表和循环多次查询的方式实现.
创建函数:
create function GetRecursion(@id int)
returns @t table(
??? id int,
??? name varchar(50),
??? parentid int
)
as
begin
??? insert @t select * from recursion where >??? while @@rowcount>0
??????? insert @t select a.* from recursion as a inner join @t as b
??????? on a.parentid=b.id and a.id not in(select id from @t)
return
end
使用方法:
select * from GetRecursion(4)
查询结果:
id?????? name?????? parentid
4?????? 产品分类???? -1
5?????? 保健品????????? 4
6?????? 医药????????????? 4
7?????? 建筑????????????? 4
三 MYSQL中的实现方法
查询语句：
select b.id,b.name,b.parentid from recursion as a, recursion as b where
a.id=b.parentid and (a.id=1 or a. parentid =1)
查询结果：
id?????? name?????? parentid
2?????? 肉类???????????? 1
3?????? 蔬菜类???????? 1
四 在ORACLE、MSSQL、MYSQL中可以使用下面的查询语句只返回树结构表的子结点数据
select *
? from tablename t
?where not exists (select ‘X’
????????? from tablename t1, tablename t2
???????? where t1.id = t2.parentid
?????????? and t1.id = t.id)
如：
select *
? from recursion t
?where not exists (select ‘X’
????????? from recursion t1, recursion t2
???????? where t1.id = t2.parentid
?????????? and t1.id = t.id)
查询结果：
id?????? name?????? parentid
2?????? 肉类???????????? 1
3?????? 蔬菜类???????? 1
5?????? 保健品???????? 4
6?????? 医药???????????? 4
7?????? 建筑???????????? 4
五 在ORACLE、MSSQL、MYSQL中可以使用下面的查询语句只返回树结构表的根结点数据
select *
? from tablename t
?where not exists (select ‘X’
????????? from tablename t1, tablename t2
???????? where t1.id = t2.parentid
?????????? and t1.id = t. parentid)
如：
select *
? from recursion t
?where not exists (select ‘X’
????????? from recursion t1, recursion t2
???????? where t1.id = t2.parentid
?????????? and t1.id = t. parentid)
查询结果：
id?????? name?????? parentid
1?????? 食品分类??? -1
4?????? 产品分类??? -1
?
关于第四，五两点的意义，其实不是很大：
（1）首先，既然是自己建树结构的表，那么对于每颗树的根节点的父节点，一般都会有统一的值，如全部存储：null，0，-1等。其实定义成统一的值，它的好处不言而喻：规范表的数据值；增加可读性，可维护性等。
本例中就是统一存放的-1。 所以没有必须再采用子查询方式，取得树的所有子节点或者所有根节点。
（2）not exists 虽然也能命中索引，但是个人觉得仍然没有通过 parentid = -1 or parentid !=-1 查询的速度快。
