--这是老大书里关于树状结构的例子 --建立个自定义函数 create function f_getchildid(@id int) returns @re table(id int) as begin insert into @re select id from tb where pid=@id while @@rowcount>0 insert into @re select a.id from tb a inner join @re b on a.pid=b.id where a.id not in(select id from @re) return end go
--单位ID 上级单位ID /* 在老大的函数的启发下整理 。 我认为表b(计划id int , 单位ID int)更为简单 */ -- declare @a table (单位ID int, 上级单位ID int ) declare @b table (计划id int , 单位ID int , 上级单位ID int ) insert into @a select 1,0 union all select 2,0 union all select 3,1 union all select 4,2 union all select 5,3insert into @b select 1,1,0 union all select 2,2,0 union all select 3,3,1 union all select 4,4,2 union all select 5,5,3 union all select 6,5,3declare @temp table (单位id int)declare @pid int ---上级单位id set @pid =0 insert into @temp select 单位id from @a where 上级单位ID = @pid while @@rowcount >0 --精华 begin insert into @temp select a.单位ID from @a a join @temp b on a.上级单位ID = b.单位ID where a.单位ID not in (select 单位ID from @temp ) --print @@rowcount ??
end select 计划id from @b where 单位id in ( select 单位id from @temp )
--建立測試環境 Create Table jihua (jh_bh Char(8), dw_bh Char(3), sj_jh_bh Char(8))Create Table danwei (dw_bh Int, sj_dw_bh Int) --插入數據 Insert jihua Select '26766703', '222', '26766703' Union All Select '84118488', '333', '26766703' Union All Select '55715452', '444', '84118488'Insert danwei Select 333, 222 Union All Select 222, 1 Union All Select 444, 222 GO --建立函數 Create Function Getsj_dw_bh(@dw_bh Int) Returns @jh_bh Table (jh_bh Char(8)) As Begin Declare @sj_dw_bh Table(dw_bh Int,sj_dw_bh Int) Insert @sj_dw_bh Select * From danwei Where dw_bh=@dw_bh While @@ROWCOUNT>0 Insert @sj_dw_bh Select B.* From @sj_dw_bh A Inner Join danwei B On A.dw_bh=B.sj_dw_bh Where B.dw_bh Not In (Select Distinct dw_bh From @sj_dw_bh) Insert @jh_bh Select jh_bh From jihua A Inner Join @sj_dw_bh B On A.dw_bh=B.dw_bh Return End GO --測試 Select * From dbo.Getsj_dw_bh(222) GO --刪除測試環境 Drop Table jihua,danwei Drop Function Getsj_dw_bh Go --結果 /* jh_bh 26766703 84118488 55715452 */
--建立个自定义函数
create function f_getchildid(@id int)
returns @re table(id int)
as
begin
insert into @re select id from tb where pid=@id
while @@rowcount>0
insert into @re select a.id
from tb a inner join @re b on a.pid=b.id
where a.id not in(select id from @re)
return
end
go
jh_bh dw_bh sj_jh_bh
26766703 222 26766703 第一条是sj_jh_bh是等于本级jh_bh 以后就是选的
84118488 333 26766703
55715452 444 84118488
//danwei(单位表)
dw_bh sj_dw_bh
333 222
222 1
444 222
/*
在老大的函数的启发下整理 。
我认为表b(计划id int , 单位ID int)更为简单
*/
--
declare @a table (单位ID int, 上级单位ID int )
declare @b table (计划id int , 单位ID int , 上级单位ID int )
insert into @a
select 1,0 union all
select 2,0 union all
select 3,1 union all
select 4,2 union all
select 5,3insert into @b
select 1,1,0 union all
select 2,2,0 union all
select 3,3,1 union all
select 4,4,2 union all
select 5,5,3 union all
select 6,5,3declare @temp table (单位id int)declare @pid int ---上级单位id
set @pid =0
insert into @temp
select 单位id from @a where 上级单位ID = @pid
while @@rowcount >0 --精华
begin
insert into @temp
select a.单位ID from @a a join @temp b on a.上级单位ID = b.单位ID
where a.单位ID not in (select 单位ID from @temp )
--print @@rowcount ??
end select 计划id from @b
where 单位id in
(
select 单位id from @temp
)
Create Table jihua
(jh_bh Char(8),
dw_bh Char(3),
sj_jh_bh Char(8))Create Table danwei
(dw_bh Int,
sj_dw_bh Int)
--插入數據
Insert jihua Select '26766703', '222', '26766703'
Union All Select '84118488', '333', '26766703'
Union All Select '55715452', '444', '84118488'Insert danwei Select 333, 222
Union All Select 222, 1
Union All Select 444, 222
GO
--建立函數
Create Function Getsj_dw_bh(@dw_bh Int)
Returns @jh_bh Table (jh_bh Char(8))
As
Begin
Declare @sj_dw_bh Table(dw_bh Int,sj_dw_bh Int)
Insert @sj_dw_bh Select * From danwei Where dw_bh=@dw_bh
While @@ROWCOUNT>0
Insert @sj_dw_bh Select B.* From @sj_dw_bh A Inner Join danwei B On A.dw_bh=B.sj_dw_bh Where B.dw_bh Not In (Select Distinct dw_bh From @sj_dw_bh)
Insert @jh_bh Select jh_bh From jihua A Inner Join @sj_dw_bh B On A.dw_bh=B.dw_bh
Return
End
GO
--測試
Select * From dbo.Getsj_dw_bh(222)
GO
--刪除測試環境
Drop Table jihua,danwei
Drop Function Getsj_dw_bh
Go
--結果
/*
jh_bh
26766703
84118488
55715452
*/