日期:2014-05-18  浏览次数:20359 次

关于一条SQl语句
Department表的结构如下 


departmentId parentId name masterId

1 0 总经办 1
2 1 事业单位 2
3 2 技术部 3
4 3 维护部 4


如果通过masterId得到它所在部门及所有下级子部门Id。。。。


如masterId = 1结果 :1234

masterId =3 结果 :34
masterId = 2结果:234
 

------解决方案--------------------
SQL code

-->Title:查找指定節點下的子結點
if object_id('Uf_GetChildID')is not null drop function Uf_GetChildID
go
create function Uf_GetChildID(@ParentID int)
returns @t table([departmentId] int)
as
begin
   insert @t select [departmentId] from [tbl] where [parentId]=@ParentID
   while @@rowcount<>0
   begin
      insert @t select a.[departmentId] from [tbl] a inner join @t b
      on a.[parentId]=b.[departmentId] and 
      not exists(select 1 from @t where [departmentId]=a.[departmentId])
   end 
return
end
go

declare @id varchar(10)
set @id=''
select @id=@id+cast([departmentId] as varchar) from
(select [departmentId]=1
union all
select * from dbo.Uf_GetChildID(1))a
print @id
select @id as [departmentId]
/*
departmentId
1234
*/

按照你的方式显示