用@@ROWCOUNT实现的SQL递归。

--> 测试数据: @T
declare @T table (id int,name varchar(12),fid int)
insert into @T
select 10001,'路容保洁',10000 union all
select 10002,'路面',10000 union all
select 10003,'路基',10000 union all
select 10004,'桥隧涵',10000 union all
select 10005,'沿线设施',10000 union all
select 10006,'机电设施',10000 union all
select 10007,'绿化',10000 union all
select 10008,'机械保洁',10001 union all
select 10009,'人工保洁',10001 union all
select 10010,'偶然事件',10001 union all
select 10011,'沥青砼路面',10002 union all
select 10012,'水泥砼路面',10002 union all
select 10013,'附属构造物',10002 union all
select 10014,'路基防护',10003 union all
select 10015,'路基水毁',10003 union all
select 10016,'路基排水',10003 union all
select 10017,'桥梁',10004 union all
select 10018,'涵洞',10004 union all
select 10019,'隧道',10004 union all
select 10020,'管理设施',10005 union all
select 10021,'安全防护设施',10005 union all
select 10022,'其他设施',10005 union all
select 10023,'桥面铺装',10017 union all
select 10024,'排水系统',10017 union all
select 10025,'附属设施',10017 union all
select 10026,'伸缩缝',10017 union all
select 10027,'翼墙锥坡',10017 union all
select 10028,'墩台',10017 union all
select 10029,'台帽盖梁',10017 union all
select 10030,'梁式构件',10017 union all
select 10031,'桥与路连接',10017

declare @R table (id int,name varchar(50),fid int)

INSERT INTO @R select * from @T T1  WHERE ID > 10022

WHILE @@ROWCOUNT>0
BEGIN
UPDATE R
SET R.NAME = T.NAME+'-'+R.NAME,R.FID = T.FID
FROM @T T,@R R WHERE R.FID = T.ID
END

SELECT * FROM @R



/*
----------结果------------

--id  name                        fid
10023 桥隧涵-桥梁-桥面铺装 10000
10024 桥隧涵-桥梁-排水系统 10000
10025 桥隧涵-桥梁-附属设施 10000
10026 桥隧涵-桥梁-伸缩缝 10000
10027 桥隧涵-桥梁-翼墙锥坡 10000
10028 桥隧涵-桥梁-墩台 10000
10029 桥隧涵-桥梁-台帽盖梁 10000
10030 桥隧涵-桥梁-梁式构件 10000
10031 桥隧涵-桥梁-桥与路连接 10000

*/