【自用】无限级分类获取SQL语句

自定义函数：

USE [ExpenseCenter_Fibrogen]
GO
/****** Object:  UserDefinedFunction [dbo].[GetSubordinateTable]    Script Date: 2014/10/11 13:24:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetSubordinateTable]
(    
    @adaccount        nvarchar(128),
    @includeResign    bit,
    @allowMore        bit
)
RETURNS @SubordinateTable TABLE 
(
    ADAccount    nvarchar(128),
    ChineseName    nvarchar(128),
    EnglishName    nvarchar(128)
)
AS
Begin
    
    Insert Into @SubordinateTable
        Select ADAccount,ChineseName,EnglishName
            From SystemUser Where ReportingUserADAccount = @adaccount
                And (@includeResign = 1 Or IsActive=1)

    if @allowMore=1
    Begin
        declare @acc    nvarchar(128)
        set @acc = ''
        while 1=1
        Begin
            Select Top 1 @acc = ADAccount From SystemUser Where ReportingUserADAccount = @adaccount And (@includeResign = 1 Or IsActive=1)
                And ADAccount>@acc Order By ADAccount

            if @@ROWCOUNT=0
                break

            Insert Into @SubordinateTable
                Select * From GetSubordinateTable(@acc,@includeResign,@allowMore)
        End
    End

    RETURN
End

 

WITH函数（仅支持SQL SERVE 2008）

向上查找

WITH Users(ADAccount,ParentADAccount) 
as 
( 
    SELECT ADAccount,ReportingUserADAccount FROM SystemUser where ADAccount='fli'
 UNION ALL
 SELECT A.ADAccount,ReportingUserADAccount FROM SystemUser A,Users b    
 where a.ADAccount = b.ParentADAccount 
)
select * from Users

 

向下查找

WITH Users(ADAccount,ParentADAccount) 
as 
( 
    SELECT ADAccount,ReportingUserADAccount FROM SystemUser where ADAccount='xwang'
 UNION ALL
 SELECT A.ADAccount,ReportingUserADAccount FROM SystemUser A,Users b    
 where a.ReportingUserADAccount = b.ADAccount 
)
select * from Users