B+树查询上下级(sql实现）

我不是女神ヾ 2022-08-06 12:28 37阅读 0赞

use pubs

\--drop table employee

create table employee  
(  
 empId char(32) primary key,  
 empName varchar(20),  
 higherUpId char(32)  
)

insert into employee values('0001','aa',null)

insert into employee values('0002','bb','0001')  
insert into employee values('0003','cc','0001')

insert into employee values('0004','dd','0002')  
insert into employee values('0005','ee','0002')

insert into employee values('0006','ff','0003')  
insert into employee values('0007','gg','0003')

insert into employee values('0008','hh','0004')  
insert into employee values('0009','ii','0004')

insert into employee values('0010','jj','0007')  
insert into employee values('0011','kk','0007')

\--查下级和间接下级  
create proc proc\_treeDownQuery  
 @id varchar(20)  
as  
 declare @temp varchar(2000)  
 declare @tempCount nvarchar(2000)  
 declare @sql varchar(5000)  
 declare @count int  
   
 set @sql = 'select empId from employee where higherUpId = ' + @id  
 set @temp = 'select empId from employee where higherUpId = '+ @id

while (1=1)  
 begin  
  set @tempCount = 'select @count=count(\*) from employee where higherUpId in (' + @temp + ')'  
  exec sp\_executesql @tempCount,N'@count int output',@count output  
   
  if (@count=0)  
   begin  
    break  
   end  
  else  
   begin  
    set @temp = 'select empId from employee where higherUpId in (' + @temp + ')'  
    set @sql = @sql +  ' union ' + @temp  
   end  
    
 end  
   
 exec(@sql)  
   
   
go

exec proc\_treeDownQuery '0001'

\--drop proc proc\_treeUpQuery

\--查上级和间接上级  
create proc proc\_treeUpQuery  
 @id varchar(20)  
as  
 declare @count int  
 declare @sql varchar(5000)  
 declare @temp varchar(2000)  
 declare @tempCount nvarchar(2000)  
   
 set @sql = 'select higherUpId from employee where empId = ' + @id  
 set @temp = 'select higherUpId from employee where empId = ' + @id  
   
 while (1=1)  
 begin   
  set @tempCount = 'select @count=count(higherUpId) from employee where empId in (' + @temp + ')'  
  exec sp\_executesql @tempCount,N'@count int output',@count output

if (@count=0)  
   begin  
    break  
   end  
  else  
   begin  
    set @temp = 'select higherUpId from employee where empId in (' + @temp + ')'  
    set @sql = @sql + ' union ' + @temp  
   end  
 end  
   
 exec(@sql)  
go

exec proc\_treeUpQuery '0009'