1 USE [ExpenseCenter_Fibrogen] 2 GO 3 /****** Object:  UserDefinedFunction [dbo].[GetSubordinateTable]    Script Date: 2014/10/11 13:24:32 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 ALTER FUNCTION [dbo].[GetSubordinateTable] 9 (    10     @adaccount        nvarchar(128),11     @includeResign    bit,12     @allowMore        bit13 )14 RETURNS @SubordinateTable TABLE 15 (16     ADAccount    nvarchar(128),17     ChineseName    nvarchar(128),18     EnglishName    nvarchar(128)19 )20 AS21 Begin22     23     Insert Into @SubordinateTable24         Select ADAccount,ChineseName,EnglishName25             From SystemUser Where ReportingUserADAccount = @adaccount26                 And (@includeResign = 1 Or IsActive=1)27 28     if @allowMore=129     Begin30         declare @acc    nvarchar(128)31         set @acc = ‘‘32         while 1=133         Begin34             Select Top 1 @acc = ADAccount From SystemUser Where ReportingUserADAccount = @adaccount And (@includeResign = 1 Or IsActive=1)35                 And ADAccount>@acc Order By ADAccount36 37             if @@ROWCOUNT=038                 break39 40             Insert Into @SubordinateTable41                 Select * From GetSubordinateTable(@acc,@includeResign,@allowMore)42         End43     End44 45     RETURN46 End

1 WITH Users(ADAccount,ParentADAccount) 2 as 3 ( 4     SELECT ADAccount,ReportingUserADAccount FROM SystemUser where ADAccount=‘fli‘5  UNION ALL6  SELECT A.ADAccount,ReportingUserADAccount FROM SystemUser A,Users b    7  where a.ADAccount = b.ParentADAccount 8 )9 select * from Users

1 WITH Users(ADAccount,ParentADAccount) 2 as 3 ( 4     SELECT ADAccount,ReportingUserADAccount FROM SystemUser where ADAccount=‘xwang‘5  UNION ALL6  SELECT A.ADAccount,ReportingUserADAccount FROM SystemUser A,Users b    7  where a.ReportingUserADAccount = b.ADAccount 8 )9 select * from Users