机构表:
--------------------------------
机构id 机构名称 上级机构id
--------------------------------
1 aaa 0
2 bbb 1
3 ccc 2
4 ddd 3
...如何实现id1是否id2的上级机构?上级机构包括了父亲的父亲的,如aaa是ddd的上级机构
--------------------------------
机构id 机构名称 上级机构id
--------------------------------
1 aaa 0
2 bbb 1
3 ccc 2
4 ddd 3
...如何实现id1是否id2的上级机构?上级机构包括了父亲的父亲的,如aaa是ddd的上级机构
@id1 int,
@id2 int
)
returns bit --1 是 0 否
as
begin
declare @id int
select @id=上级机构id from 机构表 where 机构表id=@id2
while @id<>0
begin
if @id=@id1
return 1
select @id=上级机构id from 机构表 where 机构表id=@id
end
return 0
end
go
(
id int, --机构id
name varchar(10), --机构名称
pid int -- 上级机构id
)
goinsert into t select 1,'aaa',0
union all select 2,'bbb',1
union all select 3,'bbb',2
union all select 4,'bbb',3
union all select 5,'eee',3
gocreate function IsParent(@p int,@s int)
returns bit
as
begin
declare @p1 int
select @p1=(select pid from t
where id=@s)
if(@p1=@p)
return 1 if exists(select 1 from t
where id=@p1)
return DBO.IsParent(@p,@p1) return 0
end
goselect DBO.IsParent(1,5)
select DBO.IsParent(4,5)drop function IsParent
drop table t
(
id int, --机构id
name varchar(10), --机构名称
pid int -- 上级机构id
)
goinsert into t select 1,'aaa',0
union all select 2,'bbb',1
union all select 3,'bbb',2
union all select 4,'bbb',3
union all select 5,'eee',3
gocreate function P_id(@id int) --先找到这个节点的所有的父节点
returns @tb table(id int,lev int)
as
begin
declare @i int,@l int
select @i=pid from t
where id=@id
set @l=1
insert into @tb select @i,@l
while @@rowcount>0
begin
set @L=@l+1
insert into @tb select b.pid,@l
from @tb a,t b
where a.id=b.id and lev=@l-1
end
return
endcreate function is_pid(@id1 int,@id2 int)
returns bit
begin
declare @i bit
if exists(select 1 from dbo.p_id(@id2) where id=@id1) --判断@id1是否在@id2所有的父节---点中
set @i=1
else
set @i=0
return @i
endselect dbo.is_pid(1,5)
select dbo.is_pid(4,5)