求一条SQL语句 查询所有下级

[moduofanchen]

我能查出某个单位的所有下级
---------------------------
WITH rs(
id ,[level])
AS(
select id, 0 from OrganizeInfo
where id = 31
UNION ALL
select OrganizeInfo.id, rs.[level]+1
from OrganizeInfo inner join rs on OrganizeInfo.ParentId = rs.id ) select * from rs

结果
----------------------------------
id level
----------- -----------
31 0
98 1
99 1
100 1
101 1
102 1
103 1
104 1

现在有CertificateInfo 表
存的时候单位的证件
表有自增id 单位id 以及证书信息

提问
-------------------------------------
需要得出传入单位ID所有下级的证书信息（注：所有下级 意思就是包括下级的下级 下级的下级的下级....）

[nevergu]

给你一个递归函数。
传参数就行了。

CREATE Function GetChildbase(@UnitId bigint, @Level int) 
Returns @Child Table([UnitId] int ,[username] varchar(64),[ParentUnitId] int) 
As 

Begin 

    If @Level < 1  --如果参数@Level小于“1”，则得到所有子单位 
        Begin            
            Insert @Child 
                Select ID,UserName,Pid 
                From [Login] Where inure=1 and   [Pid] = @UnitId 

	     

            While @@ROWCOUNT > 0 
	
                Begin                    
                    Insert @Child 
                        Select B.ID,B.UserName,B.Pid 
                        From @Child A Inner Join [Login] B 
                        On b.inure=1 and A.[UnitId]=B.[Pid] 
                        Where B.[Pid] Not In 
                        (Select Distinct [ParentUnitId] From @Child) 
                End 

		Insert @Child 
                Select ID,UserName,Pid 
                From [Login]  Where inure=1 and [id] = @UnitId 
        End 
    Else 
        Begin 
            Set @level = @Level -1 
            Insert @Child 
                Select id,username,Pid 
                From [Login] Where inure=1 and [Pid]=@UnitId 

	     

            While @@ROWCOUNT > 0 And @level > 0 
                Begin 
                    Set @level = @Level -1 
                    Insert @Child 
                        Select B.id,B.Username,B.Pid 
                        From @Child A Inner Join [Login] B 
                        On b.inure=1 and A.[UnitId]=B.[Pid] 
                        Where B.[Pid] Not In 
                        (Select Distinct [ParentUnitId] From @Child) 
                End 


	Insert @Child 
                Select ID,UserName,Pid 
                From [Login] Where inure=1 and [id] = @UnitId 		

        End    
    Return 
End 

[lchy110]

其实貌似这样递归的东东 我觉得放在程序里估计还好处理点

[wangtai_xiang]

没看懂

[liaoyukun111]

NNB

[xinghuihui]

是不是用存储过程会简单一点呀。

[以专业开发人员为伍]

可以转去SQL Server论坛问。