SQL使用游标遍历更新树（3层）

例子：需要将一个具有三层父子关系的表，更新列为树节点信息，更新bm列入下图

直接使用SQL游标遍历表数据，依次读取parent和id，进行Update即可；

实现代码如下：

---测试数据
create table tree_base(id int,parent int,bm varchar(200))
insert into tree_base(id,parent)
select 1,-1
union all
select 2,1
union all
select 3,1
union all
select 4,2
union all
select 6,2
union all
select 7,3
union all
select 8,3

declare @pid int;
declare @nid int;
declare @count int;
 --首先根节点只有一个，置为001
  update tree_base set bm= '00'+cast(1 as varchar) where parent not in  (select id from parent)
 --然后循环取parent_id和id，并循环更新;
  declare curson_name cursor for
     select id, parent from tree_base where bm is  null order by parent,id;
  open curson_name
  fetch next from curson_name into @nid,@pid
  while @@FETCH_STATUS=0
  begin
    --获取最后是相同节点下的第几个；
    select @count=count(1)+1 from tree_base where parent=@pid and bm is not null
	--bm由两部分构成，第一部分是父的bm，第二部分是同级第几个赋值
    update tree_base 
	set bm=(select bm from tree_base where id=@pid )
	+  right(('00'+ cast(@count as varchar(2))),3)
	where id=@nid
	--print @count
	--print @nid
	fetch next from curson_name into @nid,@pid
  end
  close curson_name
  deallocate curson_name

--select * from tree_base
--update tree_base set bm=null

知乎原文