insert into tb select 'A','B' union all select 'B','C' union all select 'C','D'
union all select 'A','A' union all select 'B','B' union all select 'C','C'
--select * from tb
create function test_f (@name varchar(20))
returns @ta table(上级部门 varchar(20))
as
begin
--select @name=上级部门 from tb where 部门=@name and 部门!=上级部门
while exists(select 1 from tb where 部门=@name and 部门!=上级部门)
begin
insert @ta select 上级部门 from tb where 部门=@name and 部门!=上级部门
select @name=上级部门 from tb where 部门=@name and 部门!=上级部门
end
return
end
insert into tb select 'A','B' union all select 'B','C' union all select 'C','D'
union all select 'A','A' union all select 'B','B' union all select 'C','C'
select * from tb
/*
A B
B C
C D
A A
B B
C C
*/
create proc ShowParentId
@subid varchar(20)
as
begin
declare @pid varchar(20)
create table #temp (pid varchar(20) primary key)
select @pid=isnull(上级部门,'0') from tb where 部门=@subid and 上级部门<>@subid
while @@rowcount<>0
begin
insert into #temp select @pid
select @pid=isnull(上级部门,'0') from tb where 部门=@pid and 上级部门<>@pid
end
select pid from #temp
drop table #temp
end
go