--测试数据
create table test1
(ASSM_NO varchar(10),
PART_NO varchar(10),
QTY_PER numeric(4,1))insert into test1
select 'AA' ,'AAB' ,1 union all
select 'AA' ,'AAC' ,2 union all
select 'AA' ,'DFKDJKFJ' ,0.5 union all
select 'AAB' ,'AAB5' ,2 union all
select 'AAB' ,'AAB6' ,4 union all
select 'AAC' ,'A54545' ,2 union all
select 'BB' ,'MMMA' ,2 union all
select 'MMMA' ,'DDDDKLKD' ,3 union all
select 'AAB5' ,'555DFDF' ,3 union all
select '555DFDF' ,'PPMC' ,2create table test2
(ASSM_NO varchar(10))insert into test2
select 'AA' union all
select 'BB'--建立function
create function f_id1()
returns @re table(ASSM_NO varchar(10),PART_NO varchar(10),QTY_PER numeric(4,1),level int)
as
begin
declare @ASSM_NO varchar(10)
declare @l int
set @l=1
declare c_c cursor for select distinct ASSM_NO from test2
open c_c
fetch next from c_c into @ASSM_NO
while(@@fetch_status=0)
begin
insert @re select @ASSM_NO,PART_NO,QTY_PER,@l from test1
where ASSM_NO=@ASSM_NO
while @@rowcount>0
begin
set @l=@l+1
insert @re select @ASSM_NO,a.PART_NO,a.QTY_PER,@l
from test1 a,@re b
where a.ASSM_NO=b.PART_NO and b.level=@l-1
end
fetch next from c_c into @ASSM_NO
end
close c_c
deallocate c_c
return
end
go--调用方式select * from dbo.f_id1()--返回ASSM_NO PART_NO QTY_PER level
---------- ---------- ------- -----------
AA AAB 1.0 1
AA AAC 2.0 1
AA DFKDJKFJ .5 1
AA AAB5 2.0 2
AA AAB6 4.0 2
AA A54545 2.0 2
AA 555DFDF 3.0 3
AA PPMC 2.0 4
BB MMMA 2.0 5
BB DDDDKLKD 3.0 6(所影响的行数为 10 行)
create table test1
(ASSM_NO varchar(10),
PART_NO varchar(10),
QTY_PER numeric(4,1))insert into test1
select 'AA' ,'AAB' ,1 union all
select 'AA' ,'AAC' ,2 union all
select 'AA' ,'DFKDJKFJ' ,0.5 union all
select 'AAB' ,'AAB5' ,2 union all
select 'AAB' ,'AAB6' ,4 union all
select 'AAC' ,'A54545' ,2 union all
select 'BB' ,'MMMA' ,2 union all
select 'MMMA' ,'DDDDKLKD' ,3 union all
select 'AAB5' ,'555DFDF' ,3 union all
select '555DFDF' ,'PPMC' ,2create table test2
(ASSM_NO varchar(10))insert into test2
select 'AA' union all
select 'BB'--建立function
create function f_id1()
returns @re table(ASSM_NO varchar(10),PART_NO varchar(10),QTY_PER numeric(4,1),level int)
as
begin
declare @ASSM_NO varchar(10)
declare @l int
set @l=1
declare c_c cursor for select distinct ASSM_NO from test2
open c_c
fetch next from c_c into @ASSM_NO
while(@@fetch_status=0)
begin
insert @re select @ASSM_NO,PART_NO,QTY_PER,@l from test1
where ASSM_NO=@ASSM_NO
while @@rowcount>0
begin
set @l=@l+1
insert @re select @ASSM_NO,a.PART_NO,a.QTY_PER,@l
from test1 a,@re b
where a.ASSM_NO=b.PART_NO and b.level=@l-1
end
fetch next from c_c into @ASSM_NO
end
close c_c
deallocate c_c
return
end
go--调用方式select * from dbo.f_id1()--返回ASSM_NO PART_NO QTY_PER level
---------- ---------- ------- -----------
AA AAB 1.0 1
AA AAC 2.0 1
AA DFKDJKFJ .5 1
AA AAB5 2.0 2
AA AAB6 4.0 2
AA A54545 2.0 2
AA 555DFDF 3.0 3
AA PPMC 2.0 4
BB MMMA 2.0 5
BB DDDDKLKD 3.0 6(所影响的行数为 10 行)
如果表test2:(上級)
assm_no:
aa
AAB
create function f_id1()
returns @re table(ASSM_NO varchar(10),PART_NO varchar(10),QTY_PER numeric(4,1),level int,i int)
as
begin
declare @ASSM_NO varchar(10)
declare @l int
declare @i int
set @l=1
declare c_c cursor for select distinct ASSM_NO from test2
open c_c
fetch next from c_c into @ASSM_NO
while(@@fetch_status=0)
begin
set @i=1
insert @re select @ASSM_NO,PART_NO,QTY_PER,@l,@i from test1
where ASSM_NO=@ASSM_NO
while @@rowcount>0
begin
set @l=@l+1
set @i=@i+1
insert @re select @ASSM_NO,a.PART_NO,a.QTY_PER,@l,@i
from test1 a,@re b
where a.ASSM_NO=b.PART_NO and b.level=@l-1
end
fetch next from c_c into @ASSM_NO
end
close c_c
deallocate c_c
return
end
goselect ASSM_NO,PART_NO,QTY_PER,i as level from dbo.f_id1()--返回ASSM_NO PART_NO QTY_PER level
---------- ---------- ------- -----------
AA AAB 1.0 1
AA AAC 2.0 1
AA DFKDJKFJ .5 1
AA AAB5 2.0 2
AA AAB6 4.0 2
AA A54545 2.0 2
AA 555DFDF 3.0 3
AA PPMC 2.0 4
AAB AAB5 2.0 1
AAB AAB6 4.0 1
AAB 555DFDF 3.0 2
AAB PPMC 2.0 3(所影响的行数为 12 行)
如果表test2:
ASSM_NO:
AA
BBselect ASSM_NO,PART_NO,QTY_PER,i as level from dbo.f_id1()--返回ASSM_NO PART_NO QTY_PER level
---------- ---------- ------- -----------
AA AAB 1.0 1
AA AAC 2.0 1
AA DFKDJKFJ .5 1
AA AAB5 2.0 2
AA AAB6 4.0 2
AA A54545 2.0 2
AA 555DFDF 3.0 3
AA PPMC 2.0 4
BB MMMA 2.0 1
BB DDDDKLKD 3.0 2(所影响的行数为 10 行)
是這樣的TEST2表中裝著上級﹐而test1裝著所有的關系﹐并且所管理的人數。
現在就想知道輸入一個上級﹐就能得到他的相對下級﹐及管理的人數﹐及層次﹐再舉一個簡單的例子﹕如果表test2:(上級)
assm_no:
aa若表test1:
ASSM_NO PART_NO QTY_PER
AA AAB 4
AAB AAC 2
AAC DFKDJKFJ 8
DFKDJKFJ TTTT 2
若表TEST2如下:
ASSM_NO
AA
則要得到如下查詢結果﹕(level表示相對上級的層次)
ASSM_NO PART_NO QTY_PER LEVEL
AA AAB 4 1
AA AAC 8 2
AA DFKDJKFJ 64 3
AA TTTT 128 4若表test2如下﹕ASSM_NO
AA
AAB則要得到如下查詢結果﹕
ASSM_NO PART_NO QTY_PER LEVEL
AA AAB 4 1
AA AAC 8 2
AA DFKDJKFJ 64 3
AA TTTT 128 4
AAB AAC 2 1
AAB DFKDJKFJ 16 2
AAB TTTT 32 3
returns @re table(ASSM_NO varchar(10),PART_NO varchar(10),QTY_PER numeric(4,1),level int)
as
begin
declare @l int
set @l=1
insert @re select @ASSM_NO,PART_NO,QTY_PER,@l from test1
where ASSM_NO=@ASSM_NO
while @@rowcount>0
begin
set @l=@l+1
insert @re select @ASSM_NO,a.PART_NO,a.QTY_PER,@l
from test1 a,@re b
where a.ASSM_NO=b.PART_NO and b.level=@l-1
end
return
end
go--求'AAB'的所有下级
select * from dbo.f_id3('AAB')ASSM_NO PART_NO QTY_PER level
---------- ---------- ------- -----------
AAB AAB5 2.0 1
AAB AAB6 4.0 1
AAB 555DFDF 3.0 2
AAB PPMC 2.0 3(所影响的行数为 4 行)
ASSM_NO PART_NO QTY_PER LEVEL
AA AAB 4 1
AA AAC 8 2
AA DFKDJKFJ 64 3
AA TTTT 128 4
AAB AAC 2 1
AAB DFKDJKFJ 16 2
AAB TTTT 32 3
declare @level intselect a.assm_no,a.part_no,a.qty_per,1 as level
into #tmpbom
from test1 a where assm_no='AA' group by a.assm_no --這里改成
set @level=1
while @level<20
begin
insert into #tmpbom
select 'AA' as assm_no,b.part_no, b.qty_per*t.qty_per as qty_per, @level+1 as level
from test1 b (nolock)
join #tmpbom t on t.part_no=b.assm_no
where t.level=@level
if @@rowcount=0
break
set @level=@level+1
end
select * from #tmpbom
http://community.csdn.net/Expert/topic/3782/3782407.xml?temp=.7346308