17,086
社区成员
发帖
与我相关
我的任务
分享
SQL> with T AS(
2 select 1 id,'张三' username,'zhangsan' usercode,null fuid,1 state,1 result,21321 liucid from dual union all
3 select 2 id,'里三' username,'lisan' usercode,null fuid,1 state,1 result,21321 liucid from dual union all
4 select 3 id,'无三' username,'wusan' usercode,null fuid,1 state,1 result,21321 liucid from dual union all
5 select 4 id,'溜溜' username,'liuliu' usercode,2 fuid,2 state,-1 result,21321 liucid from dual union all
6 select 5 id,'张明' username,'dedapo' usercode,4 fuid,2 state,0 result,21321 liucid from dual
7 )
8 SELECT * FROM(
9 select id,username,usercode,fuid,state,CONNECT_BY_ROOT(RESULT) result,liucid
10 from T T1
11 start with NOT EXISTS(SELECT 1 FROM T WHERE FUID=T1.ID)
12 CONNECT BY PRIOR fuid=ID
13 ) WHERE state='1';
ID USER USERCODE FUID STATE RESULT LIUCID
---------- ---- -------- ---------- ---------- ---------- ----------
1 张三 zhangsan 1 1 21321
3 无三 wusan 1 1 21321
2 里三 lisan 1 0 21321
SQL>
去试验了下,笔误,上面把connect_by_root的第二个下划线写成空格了,测试通过SELECT * FROM(
select id,username,usercode,fuid,state,CONNECT_BY ROOT(RESULT) result,liucid
from T T1
start with NOT EXISTS(SELECT 1 FROM T WHERE FUID=T1.ID)
CONNECT BY PRIOR fuid=ID
) WHERE state='1'