常用数据库的树形查询
表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(属于顶层的列)
语句说明:?? 13061124399
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(
????idint,
????namevarchar(50),
????parentidint
)
as
begin
????insert @tselect * 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 bwhere
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