sql 关于tree的问题

Question

树结构如下：id employee_id username age Salary higher_id
1 E001 a 32 20000 E001
2 E002 a1 30 12000 E001
3 E003 a2 30 12000 E001
4 E004 aa1 25 4000 E002
5 E005 aa2 25 4000 E002
6 E006 aa3 32 4000 E003如何得出某员工下面所有级联员工的总数 比如：员工 a 下面有 2个 （a1,a2）然后 a1 下面又有2个(aa1,aa2) ，a2 下面有一个（aa3）那总和就是 2+(2+1)=5个了。换句话说。找树杈的叶子节点总数，我这有一个，求叶子深度的代码。 如下：create function Getunderlyinglevel(@emploryee_id as varchar(50))
--获取某员工所处职位层次的深度
returns int 
as
begin 
 RETURN 
 ( 
  case when  exists (select * from employee_info where employee_id=@emploryee_id and higher_id=@emploryee_id) 
   then
    1
   else
    1 +  (select MAX(dbo.GetUnderlyingLevel(employee_id))  from employee_info where higher_id=@emploryee_id)
   end
  )
end 如果用同样的递归方法怎么得出叶子的总数?
   

Answer 1

自己先顶。我这有一个，求叶子深度的代码。 如下： create function Getunderlyinglevel(@emploryee_id as varchar(50)) 
--获取某员工所处职位层次的深度 
returns int 
as 
begin 
RETURN 
( 
  case when  exists (select * from employee_info where employee_id=@emploryee_id and higher_id=@emploryee_id) 
  then 
    1 
  else 
    1 +  (select MAX(dbo.GetUnderlyingLevel(employee_id))  from employee_info where higher_id=@emploryee_id) 
  end 
  ) 
end --如果用同样的递归方法怎么得出叶子的总数? 
  

Answer 2

前几天在刚学的create function Getunderlyinglevel(@emploryee_id as varchar(50)) 
returns int 
as 
begin 
declare @nun int
;with  cte as
(
select id from employee_info where higher_id=@emploryee_id
union all 
select b.id from cte a,employee_info b where a.id=b.higher_id
)
select @nun=count(1) from cte
RETURN @nun
end 

Answer 3

CREATE TABLE employee(id INT IDENTITY(1,1),employee_id CHAR(4),username VARCHAR(10),age INT,Salary INT,higher_id CHAR(4))
GOinsert into employee values ('E001','a','32', 20000 ,'E001') 
insert into employee values ('E002','a1','30', 12000 ,'E001') 
insert into employee values ('E003','a2','30', 12000 ,'E001') 
insert into employee values ('E004','aa1','25', 4000  ,'E002') 
insert into employee values ('E005','aa2','25', 4000  ,'E002') 
insert into employee values ('E006','aa3','32', 4000  ,'E003') 
insert into employee values ('E007','aa3','32', 4000  ,'E005') 
insert into employee values ('E008','aa3','32', 4000  ,'E003') DECLARE @t1 TABLE(employee_id CHAR(4))
INSERT INTO @t1 SELECT 'E001'DECLARE @t2 TABLE(employee_id CHAR(4))WHILE EXISTS(SELECT 1 FROM employee WHERE higher_id IN (SELECT employee_id FROM @t1) AND employee_id<>higher_id)
    BEGIN
     INSERT INTO @t2 SELECT employee_id FROM employee WHERE higher_id IN (SELECT employee_id FROM @t1) AND employee_id<>higher_id
    
     IF EXISTS(SELECT 1 FROM employee WHERE higher_id IN (SELECT employee_id FROM employee WHERE higher_id IN (SELECT employee_id FROM @t1) AND employee_id<>higher_id))
     BEGIN
     DECLARE @temp TABLE(employee_id CHAR(4))
     INSERT INTO @temp SELECT employee_id FROM employee WHERE higher_id IN (SELECT employee_id FROM @t1) AND employee_id<>higher_id
     DELETE FROM @t1
     INSERT INTO @t1 SELECT * FROM @temp
     DELETE FROM @temp
     END
     ELSE
     BEGIN
     --SELECT * FROM employee where employee_id in (select * from @t2) 
     select * from @t2
     RETURN
     END          
    ENDGODROP TABLE employee
GO
/*
employee_id 
----------- 
E002
E003
E004
E005
E006
E008
E007
*/

Answer 4

CREATE TABLE employee(id INT IDENTITY(1,1),employee_id CHAR(4),username VARCHAR(10),age INT,Salary INT,higher_id CHAR(4))
GOinsert into employee values ('E001','a','32', 20000 ,'E001') 
insert into employee values ('E002','a1','30', 12000 ,'E001') 
insert into employee values ('E003','a2','30', 12000 ,'E001') 
insert into employee values ('E004','aa1','25', 4000  ,'E002') 
insert into employee values ('E005','aa2','25', 4000  ,'E002') 
insert into employee values ('E006','aa3','32', 4000  ,'E003') 
insert into employee values ('E007','aa3','32', 4000  ,'E005') 
insert into employee values ('E008','aa3','32', 4000  ,'E003') 
GOCREATE FUNCTION GetTreeNode(@employee_id CHAR(4))
RETURNS @t2 table(id INT,employee_id CHAR(4),username VARCHAR(10),age INT,Salary INT,higher_id CHAR(4))
AS
    BEGIN
    
        DECLARE @t1 TABLE(employee_id CHAR(4))
        INSERT INTO @t1 SELECT @employee_id
        
        WHILE EXISTS(SELECT 1 FROM employee WHERE higher_id IN (SELECT employee_id FROM @t1) AND employee_id<>higher_id)
            BEGIN
             INSERT INTO @t2 SELECT * FROM employee WHERE higher_id IN (SELECT employee_id FROM @t1) AND employee_id<>higher_id
            
             IF EXISTS(SELECT 1 FROM employee WHERE higher_id IN (SELECT employee_id FROM employee WHERE higher_id IN (SELECT employee_id FROM @t1) AND employee_id<>higher_id))
             BEGIN
             DECLARE @temp TABLE(employee_id CHAR(4))
             INSERT INTO @temp SELECT employee_id FROM employee WHERE higher_id IN (SELECT employee_id FROM @t1) AND employee_id<>higher_id
             DELETE FROM @t1
             INSERT INTO @t1 SELECT * FROM @temp
             DELETE FROM @temp
             END
             ELSE
             BEGIN            
             RETURN    
             END          
            END
        RETURN                 
    END
GOSELECT * FROM dbo.GetTreeNode('E001')
GODROP TABLE employee
DROP FUNCTION GetTreeNode
GO/*
id          employee_id username   age         Salary      higher_id 
----------- ----------- ---------- ----------- ----------- --------- 
2           E002        a1         30          12000       E001
3           E003        a2         30          12000       E001
4           E004        aa1        25          4000        E002
5           E005        aa2        25          4000        E002
6           E006        aa3        32          4000        E003
8           E008        aa3        32          4000        E003
7           E007        aa3        32          4000        E005（所影响的行数为 7 行）
*/

Answer 5

我有一个思路如下，
create function Getunderlyinglevel(@emploryee_id as varchar(50),@maxcount as int) 
returns int 
as 
begin
declare @tempstr varchar(2000),@count int 
SET @tempstr=''
SELECT @tempstr=@tempstr+''''+employee_id+''',' from employee_info (nolock) where higher_id in (@emploryee_id)
Set @tempstr=left(@tempstr,len(@tempstr)-1);select @count=count(*) from employee_info where higher_id in (@tempstr)return
case when @maxcount=@count
then 
@maxcount
else
dbo.GetUnderlyingLevel(@tempstr,@count)
end
end 但是其中select @count=count(*) from employee_info where higher_id in (@tempstr)这句行不通，由于@tempstr中变量。。

Answer 6

employee_id不重复的话就没问题。

Answer 7

employee_id应该不会重复的吧,我正在想递归怎么做