表的设计
S_Employee:
[EmployeeId] [char] (13) NOT NULL ,
[Name] [varchar] (50) NULL ,
[ParentId] [char] (13) NULL DEFAULT (0)
ParentId是EmployeeId的上级,每一个EmployeeId都有一个上级ParentId,最上级的ParentId为0
请问给出一个EmployeeId如何获得他下面的第六级所有的EmployeeId?
---------------------------------------------------------------
/--按父找子--/
declare @a table (TC_Id int,TC_PID int,TC_Name varchar(200))
insert @a values(1,0,'中国')
insert @a values(2,0,'美国')
insert @a values(3,0,'加拿大')
insert @a values(4,1,'北京')
insert @a values(5,1,'上海')
insert @a values(6,1,'江苏')
insert @a values(7,6,'苏州')
insert @a values(8,7,'常熟')
insert @a values(9,6,'南京')
insert @a values(10,6,'无锡')
insert @a values(11,2,'纽约')
insert @a values(12,2,'旧金山')
declare @tmp1 table (TC_Id int,TC_PID int,TC_Name varchar(200),lev int)
insert @tmp1 select ,1 from @a where tc_ID=1
while @@rowcount>0
insert @tmp1 select a.,1 from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1)
select * from @tmp1
---------------------------------------------------------------
declare @a table ([EmployeeId] [char] (13) NOT NULL ,
[Name] [varchar] (50) NULL ,
[ParentId] [char] (13),
[grade] int)--级别
declare @i int,@EmployeeId int
set @EmployeeId=0 ----
set @i=1
insert @a select *,@i from S_Employee
where ParentId=@EmployeeId
while @i<6
begin
insert @a select A.EmployeeId,A.Name,A.ParentId,@i+1
from S_Employee A,@a B
where A.ParentId=B.EmployeeId and B.grade=@i
set @i=@i+1
end
select * from @a where grade=6
---------------------------------------------------------------
--首先创建这样的自定义函数
create function f_getbmmerg()
returns @re table(id char(13),idmerg varchar(8000),level int)
as
begin
declare @level int
set @level=1
insert into @re select Employeeid,Employeeid,@level
from S_Employee where parentid=0
while @@rowcount>0
begin
set @level=@level+1
insert into @re select b.Employeeid,a.idmerg+','+b.Employeeid,@level
from @re a inner join S_Employee b on a.employeeid=b.parentid
where a.level=@level-1
end
return
end
go