22,209
社区成员
发帖
与我相关
我的任务
分享SQL2008使用CTE递归查询的问题
数据结构
需要实现的效果
请教下各位大神，需要利用递归实现，找到某个节点下面所有的树结构，并把层级关系给梳理清楚，需要怎么来写呢？
小弟对cte 不是很熟悉，写了几次都写不出来，特上坛请教！
需要实现的效果
请教下各位大神，需要利用递归实现，找到某个节点下面所有的树结构，并把层级关系给梳理清楚，需要怎么来写呢？
小弟对cte 不是很熟悉，写了几次都写不出来，特上坛请教！
...全文
请发表友善的回复…
发表回复
wangrcaction 2019-04-23
- 打赏
- 举报
declare @table table(id int identity(1,1),[sid] int,pid int,dtrea varchar(max))
declare @value int = 123
while @value <= 143
begin
insert into @table([sid],[pid]) values(@value,case when @value > 123 then @value -1 end)
set @value += 1
end
declare @index int = 1
while exists(select 1 from (select ROW_NUMBER() OVER(ORDER BY [sid]) rownum,* from @table) as t where rownum = @index)
begin
declare @sid int = 0
select @sid = [sid] from (select ROW_NUMBER() OVER(ORDER BY [sid]) rownum,* from @table) as t where rownum = @index
declare @str varchar(max) = '' select @str = CAST(@sid as varchar(3))
declare @t_sid int = @sid
declare @level int = 1
while exists(select 1 from @table where pid = @t_sid)
begin
select @t_sid = [sid] from @table where pid = @t_sid
select @str += CHAR(10) + CHAR(13) + ' |_'+CAST(@t_sid as varchar(3)) + '('+CAST(@level as varchar(3))+'级)'
set @level += 1
end
update @table set dtrea = @str where [sid] = @sid
set @index += 1
end
select * from @table
二月十六 2019-04-22
- 打赏
- 举报
https://blog.csdn.net/sinat_28984567/article/details/79613645
建议楼主贴出数据，和对应的结果，别贴图，方便大家给你写语句
TIGER0579 2019-04-22
- 打赏
- 举报
declare @某节点 varchar(100)
WITH cte AS (SELECT * ,0 AS level FROM 表 WHERE [sid] =@某节点 UNION ALL SELECT g.*,level+1 FROM 表 g INNER JOIN cte ON g.[pid]=cte.[sid]
select *,level from cte
WITH cte AS (SELECT * ,0 AS level FROM 表 WHERE [sid] =@某节点 UNION ALL SELECT g.*,level+1 FROM 表 g INNER JOIN cte ON g.[pid]=cte.[sid]
select *,level from cte
yilanwuyu123 2019-04-22
- 打赏
- 举报
参考 #2 .......
xuxingyuan564335 2019-04-21
- 打赏
- 举报
有人帮忙解答吗？
