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

最新推荐文章于 2021-04-09 20:24:49 发布

原创最新推荐文章于 2021-04-09 20:24:49 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #insert #output #table #null

数据库开发专栏收录该内容

3 篇文章

订阅专栏

本文介绍了一个使用SQL实现的员工层级查询方法，包括直接下属及所有间接下属的查询过程，同时也展示了如何查找直接上级及其所有间接上级。通过递归方式构建查询语句，实现了对组织结构的深入探索。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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'