有一个表中有如下类似数据:
PREV_POLICY_ID POLICY_ID
318874
318874 318923
318923 318946
...其中POLICY_ID是主键.有没有比较简单的SQL语句或者方法, 我给出一个policy_id号, 能否得到以往所有的prev_policy_id号? 例如, 我给定了318946, 我希望得到318923和318874.目前我只想到了用Do select... Loop While... 之类的方法来一个一个的查, 但这法子实在笨了点也慢了点, 如果数据很多,得循环很多次. 请问各位大侠, ORACLE中有没有更简洁的办法实现这个简单功能呢? 先谢过了!
PREV_POLICY_ID POLICY_ID
318874
318874 318923
318923 318946
...其中POLICY_ID是主键.有没有比较简单的SQL语句或者方法, 我给出一个policy_id号, 能否得到以往所有的prev_policy_id号? 例如, 我给定了318946, 我希望得到318923和318874.目前我只想到了用Do select... Loop While... 之类的方法来一个一个的查, 但这法子实在笨了点也慢了点, 如果数据很多,得循环很多次. 请问各位大侠, ORACLE中有没有更简洁的办法实现这个简单功能呢? 先谢过了!
PREV_POLICY_ID POLICY_ID
318874
318874 318923
318923 318946
...
from t
start with POLICY_ID = 318874
connect by prior POLICY_ID = PREV_POLICY_ID
PREV_POLICY_ID POLICY_ID
-------------- ----------
318874
318874 318923
318923 318946
SQL>
SQL> select PREV_POLICY_ID
2 from t
3 where PREV_POLICY_ID is not null
4 start with POLICY_ID = 318874
5 connect by prior POLICY_ID = PREV_POLICY_ID;
PREV_POLICY_ID
--------------
318874
318923
select PREV_POLICY_ID from wcpolicy t
where PREV_POLICY_ID IS NOT NULL
connect by prior prev_policy_id = policy_ID --就是这里我换了一下方向
start with policy_ID=318946 ;--------------
PREV_POLICY_ID
318923
318874