declare @depName varchar(20)
set @depName='管理部'select
b.*
from
dep a,
employee b
where
a.depName = @depName
and
(a.depID=b.depID or a.depID=(select c.parent from dep c where c.depID=(select parent from dep where depID=b.depID)))
set @depName='管理部'select
b.*
from
dep a,
employee b
where
a.depName = @depName
and
(a.depID=b.depID or a.depID=(select c.parent from dep c where c.depID=(select parent from dep where depID=b.depID)))
-------------------------------------------------------------------------------------------------------------
create function f_isparent(@P_depID varchar(20),@C_depID varchar(20))
returns int
as
begin
declare @depID varchar(20)
if(@P_depID=@C_depID)
return 0
select @depID=parent from dep where depID=@C_depID
while @@rowcount<>0
begin
set @C_depID = @depID
if(@P_depID=@C_depID)
return 0
select @depID=parent from dep where depID=@C_depID
end
return -1
end
go
declare @depName varchar(20)
set @depName = '法务'
select
e.*
from
dep d,employee e
where
dbo.f_isparent(d.depID,e.depID)=0
and
depName=@depName