这样的规则sql如何写
本帖最后由 MadWork 于 2013-04-12 14:54:18 编辑 有一组数据
35000000
35010000
35010100
35010101
35010201
35020000
如何写出他们之前的父子关系
35000000是最顶级
35010000和35020000是35000000的下级
35010100是35010000的下级
35010101是35010100的下级
[解决办法]

select xh,
case 
when xh like '__000000' then '00000000'
when xh like '____0000' then SUBSTRING(xh,1,2)+'000000'
when xh like '______00' then SUBSTRING(xh,1,4)+'0000'
else SUBSTRING(xh,1,6)+'00'
end xh1
from (
select '35000000' xh
union all
select '35010000' xh
union all
select '35010100' xh
union all
select '35010101' xh
union all
select '35010201' xh
union all
select '35020000' xh
union all
select '35010200' xh
) aa

结果
xhxh1
3500000000000000
3501000035000000
3501010035010000
3501010135010100
3501020135010200
3502000035000000
3501020035010000