0--叶子节点
1--非叶子节点
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 c1,
decode((select count(*)
from t t2
where t2.c1 like t1.c1 || '%'
and t2.c1 <> t1.c1),
0,
0,
1)
from t t1;
1--非叶子节点
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 c1,
decode((select count(*)
from t t2
where t2.c1 like t1.c1 || '%'
and t2.c1 <> t1.c1),
0,
0,
1)
from t t1;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货