--> liangCK小梁 于2008-09-20
--> 生成测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id varchar(9),name Nvarchar(4))
insert into #T
select '001','中国' union all
select '001002','江苏' union all
select '001002003','南京' union all
select '001002004','镇江' union all
select '001005','上海' union all
select '001006','北京'--SQL查询如下:;WITH CTE
AS
(
SELECT id ,name,CONVERT(NVARCHAR(MAX),name) AS path
FROM #T
WHERE id ='001'
UNION ALL
SELECT t.id,t.name,CONVERT(NVARCHAR(MAX),c.path+'-'+t.name)
FROM #T AS t
JOIN CTE AS c
ON t.id LIKE c.id+'___' AND t.id<>c.id
)
SELECT
id,path
FROM CTE ORDER BY id/*
id path
--------- --------------------------------------------------
001 中国
001002 中国-江苏
001002003 中国-江苏-南京
001002004 中国-江苏-镇江
001005 中国-上海
001006 中国-北京(6 行受影响)*/
--> 生成测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id varchar(9),name Nvarchar(4))
insert into #T
select '001','中国' union all
select '001002','江苏' union all
select '001002003','南京' union all
select '001002004','镇江' union all
select '001005','上海' union all
select '001006','北京'--SQL查询如下:;WITH CTE
AS
(
SELECT id ,name,CONVERT(NVARCHAR(MAX),name) AS path
FROM #T
WHERE id ='001'
UNION ALL
SELECT t.id,t.name,CONVERT(NVARCHAR(MAX),c.path+'-'+t.name)
FROM #T AS t
JOIN CTE AS c
ON t.id LIKE c.id+'___' AND t.id<>c.id
)
SELECT
id,path
FROM CTE ORDER BY id/*
id path
--------- --------------------------------------------------
001 中国
001002 中国-江苏
001002003 中国-江苏-南京
001002004 中国-江苏-镇江
001005 中国-上海
001006 中国-北京(6 行受影响)*/
create table ss(id varchar(20),name varchar(4))
insert into ss
select '001','中国' union all
select '001002','江苏' union all
select '001002003','南京' union all
select '001002004','镇江' union all
select '001005','上海' union all
select '001006','北京'
go
--创建函数
create function f_ps(@id varchar(20))
returns varchar(1000)
as
begin
declare @sql varchar(1000)
select @sql=isnull(@sql+'-','')+name from ss where @id like id+'%'
return @sql
end
go--调用函数:
select name=dbo.f_ps(id) from ss--结果:
name --------
中国
中国-江苏
中国-江苏-南京
中国-江苏-镇江
中国-上海
中国-北京
create function f_ps(@id varchar(20))
returns varchar(1000)
as
begin
declare @sql varchar(1000)
select @sql=isnull(@sql+'-','')+name from ss where @id like id+'%' order by id --这里加个排序
return @sql
end
go
001 中国
001002 江苏
001002003 南京
001002004 镇江
001005 上海
001006 北京
如果你的表是规则的话,可以简单的这样,比较好想,地域信息是比较稳定的信息,应该可以的.
select *
from (
select a.name
from 初始表
where len(id) = 3
union
select b.name + '-' + a.name
from 初始表 a join 初始表 b on b.id = left(a.id,3)
where len(a.id) = 6
union
select b.name + '-' + a.name
from 初始表 a join 初始表 b on b.id = left(a.id,6)
where len(a.id) = 9
)
order by name
case when len(a.id)>3 then (select name=(
case when len(a.id)>6 then (select name+'-' from tb where id=left(b.id,3)) else '' end)
+name+'-' from tb b where id=left(a.id,6)) else '' end)
+name from tb a where len(a.id)<12
insert into tb
select '001','中国' union all
select '001002','江苏' union all
select '001002003','南京' union all
select '001002004','镇江' union all
select '001005','上海' union all
select '001006','北京'
select name=(
case when len(a.id)>3 then (select name=(
case when len(a.id)>6 then (select name+'-' from tb where id=left(b.id,3)) else '' end)
+name+'-' from tb b where id=left(a.id,6)) else '' end)
+name from tb a where len(a.id)<12 --如果你还有下一级行政区划,则此处要限制id长度.
go
drop table tb
/*
name
--------------
中国
江苏-江苏
中国-江苏-南京
中国-江苏-镇江
上海-上海
北京-北京(6 行受影响)
*/
create table tb (id varchar(9),name Nvarchar(4))
insert into tb
select '001','中国' union all
select '001002','江苏' union all
select '001002003','南京' union all
select '001002004','镇江' union all
select '001005','上海' union all
select '001006','北京'
--select * from tb
select name=(
case when len(a.id)>3 then (select name=(
case when len(a.id)>6 then (select name+'-' from tb where id=left(b.id,len(b.id)-3)) else '' end)
+name+'-' from tb b where id=left(a.id,len(a.id)-3)) else '' end)
+name from tb a where len(a.id)<12 --如果你还有下一级行政区划,则此处要限制id长度.
go
drop table tb
/*
name
--------------
中国
中国-江苏
中国-江苏-南京
中国-江苏-镇江
中国-上海
中国-北京*/
create table #T (id varchar(9),name Nvarchar(4))
insert into #T
select '001','中国' union all
select '001002','江苏' union all
select '001002003','南京' union all
select '001002004','镇江' union all
select '001005','上海' union all
select '001006','北京'SELECT #t.id,REPLACE(CAST(xml.query('
for $r in //row
where contains(sql:column("#t.id"),$r/@id)
return string($r/@name)'
)
AS VARCHAR(MAX)
),' ','-')
FROM
(SELECT xml=CAST((SELECT * FROM #t FOR XML RAW) AS XML)) a
CROSS JOIN #t
/*
001 中国
001002 中国-江苏
001002003 中国-江苏-南京
001002004 中国-江苏-镇江
001005 中国-上海
001006 中国-北京
*/