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

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]

--exec treeview