SQL 无限级分类的伪递归方法实例

最新推荐文章于 2021-03-12 18:27:15 发布

原创最新推荐文章于 2021-03-12 18:27:15 发布 · 554 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #insert #table

SQLServer 专栏收录该内容

12 篇文章

订阅专栏

该存储过程定义了一个名为TreeView的过程，用于生成需求数据的层次结构。它使用递归方式填充临时表@t，通过连接D_Requirement表来构建层级，并最终按虚拟字段排序返回结果。

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Alter proc [dbo].[TreeView]
as
declare @t table
(
[Virtual_Field] [varchar](80),
[Virtual_Parent] [varchar](80),
[RM_Id] [int],
[RM_Value] [varchar](50),
[RM_Comment] [varchar](50),
[PM_Status] [varchar](50),
[RM_LastUpdate] [varchar](50),
[RM_Desc] [varchar](500),
[Project_Id] [int],
[Status_Id] [int],
[Sort] [int],
[Parent_RM_Id] [int],
[Depth] [int]
)
insert @t
select cast(a.sort as varchar),cast(a.sort as varchar),* from D_Requirement a
where parent_rm_id=-1
declare @level int
set @level=1
while @level<1000
begin
set @level=@level+1

if exists(
  select * from D_Requirement
  where Parent_RM_Id in (select RM_Id from @t)
  and RM_Id not in (select RM_Id from @t)
)
  begin
   insert @t
   select
   (select [Virtual_Field] from @t where RM_Id =a.Parent_RM_Id)+'.'+cast(a.sort as varchar)
   ,(select [Virtual_Field] from @t where RM_Id =a.Parent_RM_Id)
   ,* from D_Requirement a
   where Parent_RM_Id in (select RM_Id from @t)
   and RM_Id not in (select RM_Id from @t)
  end
else
  begin
   set @level=1001
  end
end
select * from @t order by [Virtual_Field]