关于oracle的递归查询

关于oracle的递归查询

oracle的递归查询只能在查询单表时有效；若有表关联时，则无效，比如：
SELECT u.*
FROM t_user u, t_duty d, t_organization o
WHERE u.duty_id = d.id
AND d.org_id = o.id
START WITH o.id = '0207c0c90ebc0421010ebc042c5f0001'
CONNECT BY PRIOR o.id = o.parent_id

这里的表关系：user与duty是一对多；duty与organization是一对多。
上面的语句查询不出任何数据
若修改成下面这样，就可以了：把递归的查询放在一个子查询中

SELECT u.*
FROM t_user u, t_duty d
WHERE u.duty_id = d.id
AND d.org_id IN (SELECT o.id
FROM t_organization o
START WITH o.id = '0207c0c90ebc0421010ebc042c5f0001'
CONNECT BY PRIOR o.id = o.parent_id)