with t(supv, subo) as ( select 4, 5 from dual union all select 3, 4 from dual union all select 2, 3 from dual union all select 1, 2 from dual ) select max(decode(l, 1, supv)) supv4, max(decode(l, 2, supv)) supv3, max(decode(l, 3, supv)) supv2, max(decode(l, 4, supv)) supv1, max(subo) subo from ( select supv, subo, level l from t start with supv=1 connect by prior subo = supv ) t1;
with t(supv, subo) as ( select 4, 5 from dual union all select 3, 4 from dual union all select 2, 3 from dual union all select 1, 2 from dual ) select max(decode(l, 1, supv)) supv4, max(decode(l, 2, supv)) supv3, max(decode(l, 3, supv)) supv2, max(decode(l, 4, supv)) supv1, max(subo) subo from ( select supv, subo, level l from t start with supv=1 connect by prior subo = supv ) t1;
with t(supv, subo) as ( select 4, 5 from dual union all select 3, 4 from dual union all select 2, 3 from dual union all select 1, 2 from dual ) select max(decode(l, 1, supv)) supv4, max(decode(l, 2, supv)) supv3, max(decode(l, 3, supv)) supv2, max(decode(l, 4, supv)) supv1, max(subo) subo from ( select supv, subo, level l from t start with supv=1 connect by prior subo = supv ) t1;
如果是没有规律的数字呢?这个sql也不能实现啊??比如我把5改成10 把4改成7。
可以啊, with t(supv, subo) as ( select 7, 10 from dual union all select 3, 7 from dual union all select 2, 3 from dual union all select 1, 2 from dual ) select max(decode(l, 1, supv)) supv4, max(decode(l, 2, supv)) supv3, max(decode(l, 3, supv)) supv2, max(decode(l, 4, supv)) supv1, max(subo) subo from ( select supv, subo, level l from t start with supv=1 connect by prior subo = supv ) t1; 结果: SUPV4 SUPV3 SUPV2 SUPV1 SUBO ------ ------- ----- ------ ----- 1 2 3 7 10 你只要把start with supv=1 这里的1改成根节点的supv的值就行啊
谢谢你的解释。 可以解释下A和B的区别吗? -- A select supv, subo, level l from t start with supv=1 connect by prior subo = supv -- B select supv, subo, level l from t start with supv=1 connect by prior supv = subo
谢谢你还有热心回帖的朋友。问题已经解决了!!--A select supv, subo, level l from t start with supv=3 connect by prior subo = supv --B select supv, subo, level l from t start with supv=3 connect by prior supv = subo --C select supv, subo, level l from t start with subo=3 connect by prior supv = subo --D select supv, subo, level l from t start with subo=3 connect by prior subo = supv 能给出上面四条语句的解释吗?我在网上查了start with connect by的用法,但还是不是很清楚。谢谢大家了!!
select 4, 5 from dual
union all select 3, 4 from dual
union all select 2, 3 from dual
union all select 1, 2 from dual
)
select max(decode(l, 1, supv)) supv4,
max(decode(l, 2, supv)) supv3,
max(decode(l, 3, supv)) supv2,
max(decode(l, 4, supv)) supv1,
max(subo) subo
from (
select supv, subo, level l from t start with supv=1 connect by prior subo = supv
) t1;
select 4, 5 from dual
union all select 3, 4 from dual
union all select 2, 3 from dual
union all select 1, 2 from dual
)
select max(decode(l, 1, supv)) supv4,
max(decode(l, 2, supv)) supv3,
max(decode(l, 3, supv)) supv2,
max(decode(l, 4, supv)) supv1,
max(subo) subo
from (
select supv, subo, level l from t start with supv=1 connect by prior subo = supv
) t1;
select 4, 5 from dual
union all select 3, 4 from dual
union all select 2, 3 from dual
union all select 1, 2 from dual
)
select max(decode(l, 1, supv)) supv4,
max(decode(l, 2, supv)) supv3,
max(decode(l, 3, supv)) supv2,
max(decode(l, 4, supv)) supv1,
max(subo) subo
from (
select supv, subo, level l from t start with supv=1 connect by prior subo = supv
) t1;
如果是没有规律的数字呢?这个sql也不能实现啊??比如我把5改成10 把4改成7。
可以啊,
with t(supv, subo) as (
select 7, 10 from dual
union all select 3, 7 from dual
union all select 2, 3 from dual
union all select 1, 2 from dual
)
select max(decode(l, 1, supv)) supv4,
max(decode(l, 2, supv)) supv3,
max(decode(l, 3, supv)) supv2,
max(decode(l, 4, supv)) supv1,
max(subo) subo
from (
select supv, subo, level l from t start with supv=1 connect by prior subo = supv
) t1; 结果:
SUPV4 SUPV3 SUPV2 SUPV1 SUBO
------ ------- ----- ------ -----
1 2 3 7 10
你只要把start with supv=1 这里的1改成根节点的supv的值就行啊
可以解释下A和B的区别吗?
-- A
select supv, subo, level l from t start with supv=1 connect by prior subo = supv
-- B
select supv, subo, level l from t start with supv=1 connect by prior supv = subo
这是定义父子关系,
prior subo = supv,说明supv是父节点;
prior supv = subo,说明subo是父节点;看你的数据,supv是父节点。
select supv, subo, level l from t start with supv=3 connect by prior subo = supv
--B
select supv, subo, level l from t start with supv=3 connect by prior supv = subo
--C
select supv, subo, level l from t start with subo=3 connect by prior supv = subo
--D
select supv, subo, level l from t start with subo=3 connect by prior subo = supv 能给出上面四条语句的解释吗?我在网上查了start with connect by的用法,但还是不是很清楚。谢谢大家了!!