求一SQL語句(有難度﹐請高手指教,謝謝)

[remote_peng]

有表test1所有關系﹕(ASSM_NO(上級)﹐PART_NO(下級)﹐QTY_PER(個數)
ASSM_NO PART_NO QTY_PER
AA AAB 1
AA AAC 2
AA DFKDJKFJ 0.5
AAB AAB5 2
AAB AAB6 4
AAC A54545 2
BB MMMA 2
MMMA DDDDKLKD 3
AAB5 555DFDF 3
555DFDF PPMC 2
..........................


如果表test2:
ASSM_NO:
AA
BB
............



則要得到如下查詢結果﹕(level表示相對上級的層次)
ASSM_NO PART_NO QTY_PER LEVEL
AA AAB 1 1
AA AAC 2 1
AA DFKDJKFJ 0.5 1
AA AAB5 2 2
AA AAB6 4 2
AA A54545 4 2
AA 555DFDF 6 3
AA PPMC 12 4
BB MMMA 2 1
BB DDDDKLKD 6 2
.........................
如果表test2:(上級)
assm_no:
aa
AAB
則要得到如下查詢結果﹕(level表示相對上級的層次)

ASSM_NO PART_NO QTY_PER LEVEL
AA AAB 1 1
AA AAC 2 1
AA DFKDJKFJ 0.5 1
AA AAB5 2 2
AA AAB6 4 2
AA A54545 4 2
AA 555DFDF 6 3
AA PPMC 12 4
AAB AAB5 2 1
AAB AAB6 4 1
AAB 555DFDF 6 2
AAB PPMC 12 3


希望得到高手指點﹐謝謝﹗

[didoleo]

--测试数据
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' ,2

create 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 行）

[tuwicn]

惭愧！我想问一下Level的数据是什么意思？

[didoleo]

哦,稍微改一下,楼主的level要求有点不一样.这样改
如果表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
go



select 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 行）

[didoleo]

如果表test2:
ASSM_NO:
AA
BB

select 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 行）

[remote_peng]

謝謝各位的回覆:不過還有點問題﹐可能我沒有講得太清楚。
是這樣的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

[didoleo]

//現在就想知道輸入一個上級﹐就能得到他的相對下級﹐及管理的人數﹐及層次﹐再舉一個簡單的例子

那么这种情况就是把上级当一个参数传进function,在function里定义一个参数就行了嘛.

create function f_id3(@ASSM_NO varchar(10))
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 行）

[remote_peng]

TO: didoleo(冷月无声),謝謝你的回覆 ﹐不過你有沒有看清楚(QTY_PER)個數﹐如下圖﹕aa相對aab的用量是4﹐而aab相對aac的用量是2﹐所有aa相對aac的用量就是8(2*4)﹐level就是2﹐因為他們之間屬了一個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

[remote_peng]

以下是原sql語句﹐但這只能一次顯示一個上級﹐誰能改改﹐改成可以從test2表中拿X個上級
declare @level int

select 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

[lucky08]

Level字段什么意思，我还不是太理解，能否请高手解释一下。谢了！

[didoleo]

原料问题,参考这个,看邹建写的那段
http://community.csdn.net/Expert/topic/3782/3782407.xml?temp=.7346308

[remote_peng]

我這個跟它不一樣﹐它的在字段中指明了哪個是終極父目錄﹐而我這個是要根據另外一個表test2.assm_no去聯接test1.assm_no,也就是終極父目錄是不一定的。我上面那段sql一次只能做一個﹐希望改成可以做很多個。