create table ta(id varchar(20),[name] varchar(10))
insert ta select
'001','国内' union select
'002','国外' union select
'001001','江苏' union select
'001002','浙江' union select
'001003','广东' union select
'001001001','南京' union select
'001001002','无锡' union select
'001001003','苏州' union select
'001002001','杭州' union select
'001002002','温州'
go
select isnull(a.name,'') +
case when b.name is null then '' else '->' + b.name end+
case when c.name is null then '' else '->' + c.name end
from
(select id,id as pid,name from ta where len(id ) = 3) a
left join
(select id,left(id,3) as pid,name from ta where len(id) = 6) b on a.pid = b.pid
left join
(select id,left(id,3) as pid ,left(id,6) as ppid,name from ta where len(id) = 9) c
on c.ppid = b.id
drop table ta
/*
----------------------------------
国内->江苏->南京
国内->江苏->无锡
国内->江苏->苏州
国内->浙江->杭州
国内->浙江->温州
国内->广东
国外(所影响的行数为 7 行)
*/
insert ta select
'001','国内' union select
'002','国外' union select
'001001','江苏' union select
'001002','浙江' union select
'001003','广东' union select
'001001001','南京' union select
'001001002','无锡' union select
'001001003','苏州' union select
'001002001','杭州' union select
'001002002','温州'
go
select isnull(a.name,'') +
case when b.name is null then '' else '->' + b.name end+
case when c.name is null then '' else '->' + c.name end
from
(select id,id as pid,name from ta where len(id ) = 3) a
left join
(select id,left(id,3) as pid,name from ta where len(id) = 6) b on a.pid = b.pid
left join
(select id,left(id,3) as pid ,left(id,6) as ppid,name from ta where len(id) = 9) c
on c.ppid = b.id
drop table ta
/*
----------------------------------
国内->江苏->南京
国内->江苏->无锡
国内->江苏->苏州
国内->浙江->杭州
国内->浙江->温州
国内->广东
国外(所影响的行数为 7 行)
*/
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货