mysql start with递归_关于各个数据库递归（start with connect by prior）的相互转换

查找根结点(自下而上)

Oracle：

select a.id from hrmsubcompany a start with a.id=2 connect by prior a.id = a.supsubcomid；

SQL Server／DB2:

WITH allsub(id,subcompanyname,supsubcomid) as (

SELECT id,subcompanyname ,supsubcomid FROM HrmSubCompany where id=2

UNION ALL SELECT a.id,a.subcompanyname,a.supsubcomid FROM HrmSubCompany a,allsub b where a.supsubcomid = b.id

)

select * from allsub

Mysql:

-- 起始值为2，获得所有父节点(包括自己)

select t.id from (

select @id idlist,

(select @id:=group_concat(supsubcomid separator ',') from hrmsubcompany where find_in_set(id,@id)) sub

from hrmsubcompany,(select @id:=2) vars

where @id is not null) tl,hrmsubcompany t

where find_in_set(t.id,tl.idlist);

-- 起始值为2，获得所有父节点(包括自己)

select tl.lv,t.* from (

select @id idlist, @lv:=@lv+1 lv,

(select @id:=group_concat(pid separator ',') from treenodes where find_in_set(id,@id)) sub

from treenodes,(select @id:=2,@lv:=0) vars

where @id is not null) tl,treenodes t

where find_in_set(t.id,tl.idlist);

查找叶子结点，自上而下

Oracle：

select a.id from hrmsubcompany a start with a.id=2 connect by prior a.supsubcomid = a.id；

SQL Server／DB2:

WITH allsub(id,subcompanyname,supsubcomid) as (

SELECT id,subcompanyname ,supsubcomid FROM HrmSubCompany where id=2

UNION ALL SELECT a.id,a.subcompanyname,a.supsubcomid FROM HrmSubCompany a,allsub b where a.id = b.supsubcomid

)

select * from allsub

Mysql:

-- 起始值为2，获得所有父节点(包括自己)

select t.id from (

select @id idlist,

(select @id:=group_concat(id separator ',') from hrmsubcompany where find_in_set(pid,@id)) sub

from hrmsubcompany,(select @id:=2) vars

where @id is not null) tl,hrmsubcompany t

where find_in_set(t.id,tl.idlist);

-- 起始值为2，获得所有子节点(包括自己)

select tl.lv,t.* from (

select @id idlist, @lv:=@lv+1 lv,

(select @id:=group_concat(id separator ',') from treenodes where find_in_set(pid,@id)) sub

from treenodes,(select @id:=2,@lv:=0) vars

where @id is not null) tl,treenodes t

where find_in_set(t.id,tl.idlist);