set @sql = 'select empId from employee where higherUpId = ' + @id set @temp = 'select empId from employee where higherUpId = '+ @id
while (1=1) begin set @tempCount = 'select @count=count(*) from employee where higherUpId in (' + @temp + ')' exec sp_executesql @tempCount,N'@count int output',@count output
if (@count=0) begin break end else begin set @temp = 'select empId from employee where higherUpId in (' + @temp + ')' set @sql = @sql + ' union ' + @temp end
换个编码会简单很多一级部门 01 二级部门 01-01 三级部门 01-01-01一级部门的所有下级部门就用 like "01-%" 即可,方便实惠。你现在的编号么,也可以用这个方式转换一下,例如加个字段,再把编号都这样连起来放这个编号里专门做查询用。
好办法--- --建立測試環境 Create Table A (IDInt, fatherIDInt, NameVarchar(10) ) Insert A Select 1, NULL, 'tt' Union All Select 2, 1, 'aa' Union All Select 3, 1, 'bb' Union All Select 4, 2, 'cc' Union All Select 5, 2, 'gg' Union All Select 6, 4, 'yy' Union All Select 7, 4, 'jj' Union All Select 8, 7, 'll' Union All Select 9, NULL, 'uu' Union All Select 10, 9, 'oo' GO --建立函數//取字子节点 Create Function GetChildren(@ID Int) Returns @Tree Table (ID Int, fatherID Int, Name Varchar(10)) As Begin Insert @Tree Select ID, fatherID, Name From A Where fatherID = @ID While @@Rowcount > 0 Insert @Tree Select A.ID, A.fatherID, A.Name From A A Inner Join @Tree B On A.fatherID = B.ID And A.ID Not In (Select ID From @Tree) Return End GO//取父节点set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER Function [dbo].[GetParent](@ID Int) Returns @Tree Table (ID Int, fatherID Int, NameVar Varchar(10)) As Begin Insert @Tree Select ID, fatherID, NameVar From A Where ID = @ID While @@Rowcount > 0 Insert @Tree Select A.ID, A.fatherID, A.NameVar From A A Inner Join @Tree B On A.ID = B.fatherID And A.ID Not In (Select ID From @Tree) Return End--測試 Select * From dbo.GetChildren(1)Select * From dbo.GetParent(9) GO
declare @temp varchar(2000)
declare @tempCount nvarchar(2000)
declare @sql varchar(5000)
declare @count int
set @sql = 'select empId from employee where higherUpId = ' + @id
set @temp = 'select empId from employee where higherUpId = '+ @id
while (1=1)
begin
set @tempCount = 'select @count=count(*) from employee where higherUpId in (' + @temp + ')'
exec sp_executesql @tempCount,N'@count int output',@count output
if (@count=0)
begin
break
end
else
begin
set @temp = 'select empId from employee where higherUpId in (' + @temp + ')'
set @sql = @sql + ' union ' + @temp
end
end
exec(@sql)
http://blog.csdn.net/mengxj85/archive/2009/09/01/4506376.aspx
二级部门 01-01
三级部门 01-01-01一级部门的所有下级部门就用 like "01-%" 即可,方便实惠。你现在的编号么,也可以用这个方式转换一下,例如加个字段,再把编号都这样连起来放这个编号里专门做查询用。
--建立測試環境
Create Table A
(IDInt,
fatherIDInt,
NameVarchar(10)
)
Insert A Select 1, NULL, 'tt'
Union All Select 2, 1, 'aa'
Union All Select 3, 1, 'bb'
Union All Select 4, 2, 'cc'
Union All Select 5, 2, 'gg'
Union All Select 6, 4, 'yy'
Union All Select 7, 4, 'jj'
Union All Select 8, 7, 'll'
Union All Select 9, NULL, 'uu'
Union All Select 10, 9, 'oo'
GO
--建立函數//取字子节点
Create Function GetChildren(@ID Int)
Returns @Tree Table (ID Int, fatherID Int, Name Varchar(10))
As
Begin
Insert @Tree Select ID, fatherID, Name From A Where fatherID = @ID
While @@Rowcount > 0
Insert @Tree Select A.ID, A.fatherID, A.Name From A A Inner Join @Tree B On A.fatherID = B.ID And A.ID Not In (Select ID From @Tree)
Return
End
GO//取父节点set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[GetParent](@ID Int)
Returns @Tree Table (ID Int, fatherID Int, NameVar Varchar(10))
As
Begin
Insert @Tree Select ID, fatherID, NameVar From A Where ID = @ID
While @@Rowcount > 0
Insert @Tree Select A.ID, A.fatherID, A.NameVar From A A Inner Join @Tree B On A.ID = B.fatherID And A.ID Not In (Select ID From @Tree)
Return
End--測試
Select * From dbo.GetChildren(1)Select * From dbo.GetParent(9)
GO