oracle获取树结构sql,在ORACLE、MSSQL、MYSQL中树结构表递归查询的兑现方法

在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 查询的速度快。

微信 赏一包辣条吧~

支付宝 赏一听可乐吧~