oracle和mysql的递归查询方法

oracle:

1.查询一个机构下所辖机构：(start with)

select * from t00_organ t start with t.organkey=#uporgankey# connect by prior t.organkey=t.uporgankey ;

2.例子(with 查询)

with emps (employee_id, name, job_id, salary, lvl) as (

select employee_id, first_name || ', '|| last_name name, job_id, salary, 1 as lvl

from employees

where manager_id is null

union all

select emp.employee_id, emp.first_name || ', '|| emp.last_name, emp.job_id, emp.salary, root.lvl + 1

from employees emp, emps root

where emp.manager_id=root.employee_id

)

select * from emps;

mysql：

create function `fcgettreelist`(rid int)

returns varchar(1000)

begin

declare stemp varchar(1000);

declare stempchd varchar(1000);

set stemp = '$';

set stempchd =cast(rid as char);

while stempchd is not null do

set stemp = concat(stemp,',',stempchd);

select group_concat(id) into stempchd from treenodes where find_in_set(pid,stempchd)>0;

end while;

return stemp;

end

-- 调用

select * from test where find_in_set(id,fcgettreelist(1));