sql server 利用with as实现递归算法，应用计算层级，

关键的语法是 with as，具体用法见官方

https://docs.microsoft.com/zh-cn/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15

结合其他网友的博文：

https://www.imooc.com/article/27689

https://blog.csdn.net/johnf_nash/article/details/78681060 

 declare @networkno varchar(10) ,@networklevel tinyint,@bankpath varchar(500),@all varchar(8000)
  declare cur cursor for 
   WITH TempNetwork(NetworkNO,ParentNO,Name,BankPath_,NetworkLevel_) as
   (
   select NetworkNO,ParentNO,Name,CAST(Name as varchar(500)) as BankPath_,1 as NetworkLevel_ from WFT_Network WHERE ParentNO='0'
   UNION ALL
   SELECT n.NetworkNO,n.ParentNO,n.Name,CAST(tn.BankPath_+'/'+n.Name as varchar(500)) as BankPath_, tn.NetworkLevel_+1 as NetworkLevel_ FROM TempNetwork tn,WFT_Network n WHERE tn.NetworkNO=n.ParentNO
   )
   select NetworkNO,Name as BankPath_,0 as NetworkLevel_ from WFT_Network WHERE NetworkNO='0'
   union all
   select NetworkNO,BankPath_,NetworkLevel_ from TempNetwork
  --)
  open cur
  fetch next from cur into @networkno,@bankpath,@networklevel
  while(@@FETCH_STATUS<>-1)
  begin  
    set @all=''   
    declare @networkno_inner varchar(10)
    declare cur_inner cursor for 
    with Tb as
    (
    select NetworkNO,ParentNO FROM WFT_Network WHERE NetworkNO=@networkno union all 
    select WFT_Network.NetworkNO,WFT_Network.ParentNO FROM WFT_Network,Tb where WFT_Network.ParentNO=Tb.NetworkNO
    )
    select NetworkNO  FROM Tb 
    open cur_inner
    fetch next from cur_inner into @networkno_inner
    while(@@FETCH_STATUS<>-1)
    begin
    set @all=@all+','+@networkno_inner
    fetch next from cur_inner into @networkno_inner
    end
    close cur_inner
    deallocate cur_inner
   --print  @all+','  
   --修改数据
   update WFT_Network SET AllChildNetworkNO_=@all+',',NetworkLevel_=@networklevel,Properties_=(CASE WHEN @networklevel=0 then '分行' when @networklevel=1 then '1级支行' when @networklevel=1 then '2级支行' else '网点' end),BankPath_=@bankpath where NetworkNO=@networkno
   fetch next from cur into @networkno,@bankpath,@networklevel
  end
  close cur
  deallocate cur

用到脚本如下：插入前面三列数据，生成后面四列的数据

USE [jj360]
GO
/****** Object:  Table [dbo].[WFT_Network]    Script Date: 2020-08-13 11:17:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[WFT_Network](
	[NetworkNO] [varchar](10) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[ParentNO] [varchar](10) NULL,	
	[AllChildNetworkNO] [varchar](8000) NULL,	
	[NetworkLevel] [tinyint] NULL,
	[Properties] [varchar](10) NULL,
	[BankPath] [varchar](500) NULL,	
 CONSTRAINT [PK_WFT_Network] PRIMARY KEY CLUSTERED 
(
	[NetworkNO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO