日期:2014-05-18 浏览次数:20588 次
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 行) */