CREATE function [dbo].[GetChildAccount](@ID varchar(10))
returns @t table(ID varchar(10),ParentID varchar(10),Level int)
as
begin
declare @i int
set @i = 1
insert into @t select @ID,@ID,0 --当前级,本级,如果不要的话可以注释掉或再加个参数来选择操作
insert into @t select AccountID,ParentID,@i from Account where ParentID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.AccountID,a.ParentID,@i
from
Account a,@t b
where
a.ParentID=b.ID and b.Level = @i-1
end
return
end
------------------------------------------------------------------------------------------------
表机构如下:
SELECT [AccountID] ,[Name] ,[ParentID] ,[Type] FROM [Attractions].[dbo].[Account]
查询结果如下:
使用函数查询:
SELECT [AccountID] ,[Name] ,[ParentID] ,[Type] FROM [Attractions].[dbo].[Account]
where [AccountID] in (Select ID from dbo.GetChildAccount(2))
查询结果如下: