表citysort结构如下id cityid fid cityname
1 10 0 上海
2 1010 10 浦东
3 1011 10 南汇
3 11 0 某省
3 1110 11 某州
3 111010 1110 某县要求返还如下视图cityid cityname
10 上海
1010 上海-浦东
1011 上海-南汇
11 某省
1110 某省-某州
111010 某省-某州-某县递推的条件为，直到fid=0为止
1 10 0 上海
2 1010 10 浦东
3 1011 10 南汇
3 11 0 某省
3 1110 11 某州
3 111010 1110 某县要求返还如下视图cityid cityname
10 上海
1010 上海-浦东
1011 上海-南汇
11 某省
1110 某省-某州
111010 某省-某州-某县递推的条件为，直到fid=0为止
create table [tb]([modeid] int,modename varchar(20),parentid int)
insert [tb]
select 100 ,'商品管理', 0 union all
select 101 ,'定单管理', 0 union all
select 102 ,'用户管理', 0 union all
select 104 ,'学院广告', 0 union all
select 105 ,'系统设置', 0 union all
select 106 ,'附件管理', 0 union all
select 107 ,'商品管理', 100 union all
select 108 ,'明细管理', 100 union all
select 109 ,'物流管理', 100 union all
select 110 ,'商品信息管理', 107 union all
select 111 ,'商品分类管理', 107 union all
select 112 ,'回收站管理', 107 union all
select 114 ,'团购管理', 108 union all
select 115 ,'拍卖管理', 108 union all
select 116 ,'优惠管理', 108 union all
select 117 ,'会员管理', 102 union all
select 118 ,'会员卡管理', 102 union all
select 119 ,'资金管理', 102 union all
select 120 ,'管理员管理', 102 union all
select 121 ,'添加管理员', 120 union all
select 122 ,'修改管理员', 120
go
--查所有子结点
if object_id('f_getC') is not null drop function f_getC
go
create function f_getC(@id int)
returns @re table(id int,level int,sort varchar(10))
as
begin
declare @l int
set @l=0
insert @re select @id,@l,null
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.modeid,@l,ltrim(isnull(b.sort,a.modeid)) from tb as a,@re as b
where b.id=a.parentid and b.level=@l-1
end
update @re set level = level -1
return
end
go
select a.modeid,a.parentid,REPLICATE(' ',b.level) +'┝'+a.modename,b.level,b.sort from tb a,f_getC(0) b
where a.modeid=b.id
order by case when b.level<2 then 0 else 1 end,b.sort,b.level/*
modeid parentid sort level
----------- ----------- -------------------------------------------------- ---------- -----------
100 0 ┝商品管理 100 0
107 100 ┝商品管理 100 1
108 100 ┝明细管理 100 1
109 100 ┝物流管理 100 1
101 0 ┝定单管理 101 0
102 0 ┝用户管理 102 0
117 102 ┝会员管理 102 1
118 102 ┝会员卡管理 102 1
119 102 ┝资金管理 102 1
120 102 ┝管理员管理 102 1
104 0 ┝学院广告 104 0
105 0 ┝系统设置 105 0
106 0 ┝附件管理 106 0
110 107 ┝商品信息管理 100 2
111 107 ┝商品分类管理 100 2
112 107 ┝回收站管理 100 2
114 108 ┝团购管理 100 2
115 108 ┝拍卖管理 100 2
116 108 ┝优惠管理 100 2
121 120 ┝添加管理员 102 2
122 120 ┝修改管理员 102 2（所影响的行数为 21 行）*/
--查所有子结点,带路径与排序
if object_id('f_getC') is not null drop function f_getC
go
create function f_getC(@id int)
returns @re table(id int,level int,sort varchar(100),path varchar(500))
as
begin
declare @l int
set @l=0
insert @re
select [modeid],@l,right('00000'+ltrim(modeid),5),modename
from tb where parentid=@id
while @@rowcount>0
begin
set @l=@l+1
insert @re
select a.modeid,@l,b.sort+right('00000'+ltrim(a.modeid),5),
b.path+' - '+a.modename
from tb as a,@re as b
where b.id=a.parentid and b.level=@l-1
end
update @re set level = level
return
end
go select a.modeid,a.parentid,REPLICATE(' ',b.level) +'┝'+a.modename,b.level,b.sort ,b.path from tb a,f_getC(0) b
where a.modeid=b.id
order by sort/*
modeid parentid level
----------- ----------- -------------------- ----------- -------------------- ----------------------------------------
100 0 ┝商品管理 0 00100 商品管理
107 100 ┝商品管理 1 0010000107 商品管理 - 商品管理
110 107 ┝商品信息管理 2 001000010700110 商品管理 - 商品管理 - 商品信息管理
111 107 ┝商品分类管理 2 001000010700111 商品管理 - 商品管理 - 商品分类管理
112 107 ┝回收站管理 2 001000010700112 商品管理 - 商品管理 - 回收站管理
108 100 ┝明细管理 1 0010000108 商品管理 - 明细管理
114 108 ┝团购管理 2 001000010800114 商品管理 - 明细管理 - 团购管理
115 108 ┝拍卖管理 2 001000010800115 商品管理 - 明细管理 - 拍卖管理
116 108 ┝优惠管理 2 001000010800116 商品管理 - 明细管理 - 优惠管理
109 100 ┝物流管理 1 0010000109 商品管理 - 物流管理
101 0 ┝定单管理 0 00101 定单管理
102 0 ┝用户管理 0 00102 用户管理
117 102 ┝会员管理 1 0010200117 用户管理 - 会员管理
118 102 ┝会员卡管理 1 0010200118 用户管理 - 会员卡管理
119 102 ┝资金管理 1 0010200119 用户管理 - 资金管理
120 102 ┝管理员管理 1 0010200120 用户管理 - 管理员管理
121 120 ┝添加管理员 2 001020012000121 用户管理 - 管理员管理 - 添加管理员
122 120 ┝修改管理员 2 001020012000122 用户管理 - 管理员管理 - 修改管理员
104 0 ┝学院广告 0 00104 学院广告
105 0 ┝系统设置 0 00105 系统设置
106 0 ┝附件管理 0 00106 附件管理(21 行受影响)
*/----------
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
id int,
cityid varchar(10),
fid varchar(10),
cityname varchar(10)
)
go
--插入测试数据
insert into tb select 1,'10','0','上海'
union all select 2,'1010','10','浦东'
union all select 3,'1011','10','南汇'
union all select 4,'11','0','某省'
union all select 5,'1110','11','某州'
union all select 6,'111010','1110','某县'
union all select 7,'11101010','111010','某镇'
go
--代码实现create function f_test()
returns @temp table(cityid varchar(10),cityname varchar(max))
as
begin
;with cte as(
select cityid,cityname=cast(cityname as varchar(max)) from tb where fid='0'
union all
select a.cityid,cityname=b.cityname+'-'+a.cityname from tb a,cte b where a.fid=b.cityid
)
insert @temp
select * from cte order by cityid
return
end
goselect * from f_test()/*测试结果cityid cityname
---------------------
10 上海
1010 上海-浦东
1011 上海-南汇
11 某省
1110 某省-某州
111010 某省-某州-某县
11101010 某省-某州-某县-某镇(7 行受影响)
*/select * from f_test() where charindex('上海',cityname)>0/*测试结果cityid cityname
---------------------
10 上海
1010 上海-浦东
1011 上海-南汇(3 行受影响)
*/
select
'1', '10', '0', N'上海' union select
2, 1010, 10, N'浦东' union select
3, 1011, 10, N'南汇' union select
3, 11, 0, N'某省' union select
3, 1110, 11, N'某州' union select
3, 111010, 1110, N'某县'
with t1 as
(
select row_number() over(order by id,cityid) as num ,* from #t
), act as
(
select * from t1
union all
select b.num,a.id,a.cityid,a.fid,a.cityname from t1 a join act b on a.cityid =b.fid
)select max(cityid) as cityid,
cityname =stuff( (select '-'+cityname from act where num =a.num order by cityid desc for xml path('')),1,1,'')
from act a group by a.num/*
cityid cityname
----------- -
10 上海
1010 浦东-上海
11 某省
1011 南汇-上海
1110 某州-某省
111010 某县-某州-某省(6 row(s) affected)
改下不是倒序select '-'+cityname from act where num =a.num order by cityid desc for xml path('') with t1 as
(
select row_number() over(order by id,cityid) as num ,* from #t
), act as
(
select * from t1
union all
select b.num,a.id,a.cityid,a.fid,a.cityname from t1 a join act b on a.cityid =b.fid
)
select max(cityid) as cityid,
cityname =stuff( (select '-'+cityname from act where num =a.num order by cityid for xml path('')),1,1,'')
from act a group by a.num