1--非叶子 0-叶子节点lvl为层次:with t as (select '1002' c1 from dual union all select '100201' c1 from dual union all select '1002018523532' c1 from dual union all select '100201435X46413' c1 from dual union all select '100202' c1 from dual union all select '1002026335332' c1 from dual union all select '2001' c1 from dual union all select '200101' c1 from dual union all select '200102' c1 from dual) select t3.c1, t3.lvl, t4.is_leaf from (select t2.c1, count(1) lvl from t t1, t t2 where t2.c1 like t1.c1 || '%' group by t2.c1 order by t2.c1) t3, (select c1, decode((select count(*) from t t2 where t2.c1 like t1.c1 || '%' and t2.c1 <> t1.c1), 0, 0, 1) is_leaf from t t1) t4 where t3.c1 = t4.c1;
1--非叶子 0-叶子节点lvl为层次:with t as (select '1002' c1 from dual union all select '100201' c1 from dual union all select '1002018523532' c1 from dual union all select '100201435X46413' c1 from dual union all select '100202' c1 from dual union all select '1002026335332' c1 from dual union all select '2001' c1 from dual union all select '200101' c1 from dual union all select '200102' c1 from dual) select t3.c1, t3.lvl, t4.is_leaf from (select t2.c1, count(1) lvl from t t1, t t2 where t2.c1 like t1.c1 || '%' group by t2.c1 order by t2.c1) t3, (select c1, decode((select count(*) from t t2 where t2.c1 like t1.c1 || '%' and t2.c1 <> t1.c1), 0, 0, 1) is_leaf from t t1) t4 where t3.c1 = t4.c1; ++
0-叶子节点lvl为层次:with t as
(select '1002' c1
from dual
union all
select '100201' c1
from dual
union all
select '1002018523532' c1
from dual
union all
select '100201435X46413' c1
from dual
union all
select '100202' c1
from dual
union all
select '1002026335332' c1
from dual
union all
select '2001' c1
from dual
union all
select '200101' c1
from dual
union all
select '200102' c1
from dual)
select t3.c1, t3.lvl, t4.is_leaf
from (select t2.c1, count(1) lvl
from t t1, t t2
where t2.c1 like t1.c1 || '%'
group by t2.c1
order by t2.c1) t3,
(select c1,
decode((select count(*)
from t t2
where t2.c1 like t1.c1 || '%'
and t2.c1 <> t1.c1),
0,
0,
1) is_leaf
from t t1) t4
where t3.c1 = t4.c1;
0-叶子节点lvl为层次:with t as
(select '1002' c1
from dual
union all
select '100201' c1
from dual
union all
select '1002018523532' c1
from dual
union all
select '100201435X46413' c1
from dual
union all
select '100202' c1
from dual
union all
select '1002026335332' c1
from dual
union all
select '2001' c1
from dual
union all
select '200101' c1
from dual
union all
select '200102' c1
from dual)
select t3.c1, t3.lvl, t4.is_leaf
from (select t2.c1, count(1) lvl
from t t1, t t2
where t2.c1 like t1.c1 || '%'
group by t2.c1
order by t2.c1) t3,
(select c1,
decode((select count(*)
from t t2
where t2.c1 like t1.c1 || '%'
and t2.c1 <> t1.c1),
0,
0,
1) is_leaf
from t t1) t4
where t3.c1 = t4.c1;
++