SELECT A.wf_id,a.parentid,level,sys_connect_by_path(A.wf_id,'/') path
FROM a
START WITH A.PARENTID IS NULL
CONNECT BY PRIOR A.WF_ID = A.PARENTID
SZ_120604161301_3000 1 /SZ_120604161301_3000
SZ_120606095746_root 1 /SZ_120606095746_root
SZ_120606145252_3000 1 /SZ_120606145252_3000
SZ_120606150143_3000 1 /SZ_120606150143_3000
SZ_120606182414_root 1 /SZ_120606182414_root
SZ_120607170535_3000 1 /SZ_120607170535_3000
SZ_120608102517_3000 1 /SZ_120608102517_3000
SZ_120611172040_3000 1 /SZ_120611172040_3000
SZ_120611174615_3000 1 /SZ_120611174615_3000
SZ_120615105928_3000 1 /SZ_120615105928_3000
SZ_120617174458_3000 1 /SZ_120617174458_3000
SZ_120617174639_3000 SZ_120617174458_3000 2 /SZ_120617174458_3000/SZ_120617174639_3000
SZ_120617174932_3000 SZ_120617174639_3000 3 /SZ_120617174458_3000/SZ_120617174639_3000/SZ_120617174932_3000
我想要的数据是:
SZ_120617174458_3000 1 /SZ_120617174458_3000
SZ_120617174639_3000 SZ_120617174458_3000 2 /SZ_120617174458_3000/SZ_120617174639_3000
SZ_120617174932_3000 SZ_120617174639_3000 3 /SZ_120617174458_3000/SZ_120617174639_3000/SZ_120617174932_3000
请问高手们这个sql该咋写?
FROM a
START WITH A.PARENTID IS NULL
CONNECT BY PRIOR A.WF_ID = A.PARENTID
SZ_120604161301_3000 1 /SZ_120604161301_3000
SZ_120606095746_root 1 /SZ_120606095746_root
SZ_120606145252_3000 1 /SZ_120606145252_3000
SZ_120606150143_3000 1 /SZ_120606150143_3000
SZ_120606182414_root 1 /SZ_120606182414_root
SZ_120607170535_3000 1 /SZ_120607170535_3000
SZ_120608102517_3000 1 /SZ_120608102517_3000
SZ_120611172040_3000 1 /SZ_120611172040_3000
SZ_120611174615_3000 1 /SZ_120611174615_3000
SZ_120615105928_3000 1 /SZ_120615105928_3000
SZ_120617174458_3000 1 /SZ_120617174458_3000
SZ_120617174639_3000 SZ_120617174458_3000 2 /SZ_120617174458_3000/SZ_120617174639_3000
SZ_120617174932_3000 SZ_120617174639_3000 3 /SZ_120617174458_3000/SZ_120617174639_3000/SZ_120617174932_3000
我想要的数据是:
SZ_120617174458_3000 1 /SZ_120617174458_3000
SZ_120617174639_3000 SZ_120617174458_3000 2 /SZ_120617174458_3000/SZ_120617174639_3000
SZ_120617174932_3000 SZ_120617174639_3000 3 /SZ_120617174458_3000/SZ_120617174639_3000/SZ_120617174932_3000
请问高手们这个sql该咋写?
SELECT A.wf_id,a.parentid,level,sys_connect_by_path(A.wf_id,'/') path
FROM a
START WITH A.PARENTID IS NULL
CONNECT BY PRIOR A.WF_ID = A.PARENTID
) tab where level > 1
SZ_120604161301_3000 1 /SZ_120604161301_3000 这个只有1层就不显示了,
SZ_120617174458_3000 1 /SZ_120617174458_3000这个有3层就把3层都显示出来,
SZ_120617174458_3000 1 /SZ_120617174458_3000
SZ_120617174639_3000 SZ_120617174458_3000 2 /SZ_120617174458_3000/SZ_120617174639_3000
SZ_120617174932_3000 SZ_120617174639_3000 3 /SZ_120617174458_3000/SZ_120617174639_3000/SZ_120617174932_3000