部门表
aid aname aparentid
1 a1 0
2 a2 0
3 a3 1
4 a4 2
5 a5 3员工表
uid uname aid
001 b1 1
002 b2 5
003 b3 1
004 b4 4
005 b5 3
006 b6 2aid为部门表的部门id,aname为部门名,aparentid为该部门的上一级部门
uid为员工表的员工编号,uname为员工名,aid为该员工所处的最低层的那个部门的部门id要实现查找跟自己同一个部门的所有员工的id,包括该部门的下属部门的所有员工的id比如我给出员工id为 001 的那位员工就可以查找出(001,003,005)这三个员工编号
给出员工id为 006 的就可以查出(004,006)
aid aname aparentid
1 a1 0
2 a2 0
3 a3 1
4 a4 2
5 a5 3员工表
uid uname aid
001 b1 1
002 b2 5
003 b3 1
004 b4 4
005 b5 3
006 b6 2aid为部门表的部门id,aname为部门名,aparentid为该部门的上一级部门
uid为员工表的员工编号,uname为员工名,aid为该员工所处的最低层的那个部门的部门id要实现查找跟自己同一个部门的所有员工的id,包括该部门的下属部门的所有员工的id比如我给出员工id为 001 的那位员工就可以查找出(001,003,005)这三个员工编号
给出员工id为 006 的就可以查出(004,006)
UNION SELECT a.uid FROM 员工表 WHERE aid IN (SELECT b.aid FROM 员工表 a,部门表 b WHERE a.uid='001' AND a.aid = b.aparentid)
/*
create table depart(aid int ,aname varchar(8),aparentid int )
create table person (uid varchar(8),uname varchar(8),aid int)
insert depart select 1,'a1', 0 union select 2,'a2', 0 union select 3,'a3',1 union select 4,'a4',2 union select 5,'a5',3
insert person select '001','b1',1 union select '002','b2',5 union select '003','b3',1 union select '004','b4',4 union select '005','b5',3 union select '006','b6',2
*/
set nocount on
declare @t1 table(aid varchar(10))
declare @t2 table(aid varchar(10))
declare @aid varchar(100),@aid1 varchar(100)
declare @no1 int,@no2 int
select @aid1=aid from person where uid='001'
set @aid=@aid1
insert into @t1 select @aid ; insert into @t2 select @aid1
set @no1=0 ; set @no2=0 ; declare @flag bit ; set @flag=0
while @flag=0
begin
insert into @t1 select cast(aid as varchar) from depart a where exists( select aid from @t1 where aid=a.aparentid)
set @no1=@@rowcount
if @no1=@no2
set @flag=1
else
set @no2=@no1
end
set @flag=0 ;set @no1=0 ;set @no2=0
while @flag=0
begin
insert into @t2 select cast(aparentid as varchar) from depart a where exists( select aid from @t2 where aid=a.aid)
set @no1=@@rowcount
if @no1=@no2
set @flag=1
else
set @no2=@no1
endselect a.* from person a, (select * from @t2 union select * from @t1) b
where a.aid=b.aid
set nocount off