sql server查询问题
0
[已关闭问题]
001 中国
002 国外
001001 江苏
001002 山东
001001001 南京
001001002 苏州
001002001 烟台
001002002 济南
用SQL语句实现一下结果：
001 中国 --
001001 中国 江苏
001001001 中国 江苏 南京
001001002 中国 江苏 苏州
001002001 中国 山东 烟台
001002002 中国 山东 济南
大家帮帮忙看看改怎么写sql语句 ~
其他回答(3)
0
先投机取巧，有时间再改进：
CREATE TABLE tb(id varchar(20), address varchar(10))
INSERT tb SELECT '001','中国'
UNION ALL SELECT '002','国外'
UNION ALL SELECT '001001','江苏'
UNION ALL SELECT '001002','山东'
UNION ALL SELECT '001001001','南京'
UNION ALL SELECT '001001002','苏州'
UNION ALL SELECT '001002001','烟台'
UNION ALL SELECT '001002002','济南'
--select * from tb
select country=
case
when substring(id,1,3)='002' then '国外'
when substring(id,1,3)='001' then '中国'
end,
province=
case
when (len(id)=6 or len(id)=9 ) and substring(id,4,3)='001' then '江苏'
when (len(id)=6 or len(id)=9 ) and substring(id,4,3)='002' then '山东'
end,
erea=
case
when len(id)=9 and substring(id,7,3)='001' then '烟台'
when len(id)=9 and substring(id,7,3)='002' then '济南'
end
from tb
0
mark...
0
select code,
(case len(code)/3
when 1 then name
when 2 then
((select a.name from tbl a
where a.code=substring(code,1,3)) + ' ' +
(select b.name from tbl b
where b.code=substring(code,4,len(code)))
)
when 4 then ....
end
) as name
from tbl
0
SELECT T.id,A.address as country,B.address as provence,C.address as city
from tb as T
left join tb as A
on left(T.id,3) =A.id
and len(A.id) = 3
left join tb as B
on left(T.id,6) = B.id
and len(B.id) = 6
left join tb as C
on left(T.id,9) = C.id
and len(C.id) = 9
