B+树查询上下级(sql实现）
use pubs
--drop table employee
create table employee
(
empId char(32) primary key,
empName varchar(20),
higherUpId char(32)
)
(
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('0003','cc','0001')
insert into employee values('0004','dd','0002')
insert into employee values('0005','ee','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('0007','gg','0003')
insert into employee values('0008','hh','0004')
insert into employee values('0009','ii','0004')
insert into employee values('0009','ii','0004')
insert into employee values('0010','jj','0007')
insert into employee values('0011','kk','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
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
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
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'
【推荐】腾讯云新用户域名抢购1元起，抓紧抢购
· 精准率首次超过人类！阿里巴巴机器阅读理解打破世界纪录
· 技术帖：每天被今日头条推送文章 背后的算法技术是什么？
· 支付宝实体版老黄历问世：全球限量1000册
· 趣店被蚂蚁金服送上纽交所，现在是时候该独立了
· 蚂蚁宝卡升级：支付宝/微博即将免流
» 更多新闻...
· 以操作系统的角度述说线程与进程
· 软件测试转型之路
· 门内门外看招聘
· 大道至简，职场上做人做事做管理