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));