17,086
社区成员
发帖
与我相关
我的任务
分享
select * from A
start with child='child1'
connect by prior parent=child and prior status='Y'
and prior start_date<=sysdate and prior end_date>=sysdate
select * from A
start with child='child1'
connect by prior child=parent -- 你方向搞反了把,向上递归其实子件才是“上级”
and ( (prior child='child1') -- 先保证开始的子件连上父件
OR(-- 然后再对已经连进来的父件进行判断,决定是否连父件的父件
prior status='Y'
and prior start_date<=sysdate
and prior end_date>=sysdate
)
)
connect by prior parent=child and status='Y'
and start_date<=sysdate and end_date>=sysdate
[/quote]
放在连接条件里面会影响递归的,楼主应该要的就是这种结果啊。[/quote]
打个比方,我想要父件status为"Y"的继续递归,但不为Y的话,最后一层会把所有的状态都查出来,比如最后一层status为"N"的也是我
需要的.
如果按你们的那样写法,最后一层status为"N"的也会被过虑掉的connect by prior parent=child and status='Y'
and start_date<=sysdate and end_date>=sysdate
[/quote]
放在连接条件里面会影响递归的,楼主应该要的就是这种结果啊。connect by prior parent=child and status='Y'
and start_date<=sysdate and end_date>=sysdate