oracle和mysql的递归查询方法，oraclemysql递归
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));