未测试,基本思路是构建树形查询,利用获取根节点的值的方式得到最终结果 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'
不行,好像提示ORA-00923: 未找到要求的 FROM 关键字 00923. 00000 - "FROM keyword not found where expected" *Cause: *Action: 行 19 列 17 出错能不能在写一下,谢谢
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 21321SQL> 去试验了下,笔误,上面把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'
不行,好像提示ORA-00923: 未找到要求的 FROM 关键字
00923. 00000 - "FROM keyword not found where expected"
*Cause:
*Action:
行 19 列 17 出错能不能在写一下,谢谢
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 21321SQL>
去试验了下,笔误,上面把connect_by_root的第二个下划线写成空格了,测试通过