求SQL BOM相关(SQL2000)

[wolftomb]

主表SP_ProductP
ProductP_ID ProductP_PID ProductP_Order
11 19 1
12 19 2
15 19 3

明细表SP_ProductPItem(ProductPI_PID=ProductP_ID)
ProductPI_PID ProductPI_Type ProductPI_MHNO ProductPI_UQty
11 2 BD0001 11
11 2 BD0003 20
11 2 BD0022 5

12 2 BD0005 3
12 4 H-HP-WH2980 2
12 2 BD0001 2

15 4 H2-HP-WH2980 3
15 2 BD0008 5

主表SP_ProductP
此处ID为19货品由3步工序组成，每一步所需要的原料（半成品）保存在SP_ProductPItem中,最后一步为(ProductP_Order最大值此处为3)

第1步工序的产出物（半成品）为H-HP-WH2980
第2步工序的产出物（半成品）为H2-HP-WH2980
第3步生成ID为19的货品

SP_ProductPItem表中
ProductPI_PID主表ID
ProductPI_Type类型2为原材料,4为半成品
ProductPI_MHNO原材料或者半成品编号
ProductPI_UQty单位使用量

现在我想得到做一个货品所需要的原材料数量的SQL。

比如这里ID 19的货品需要原料数量为
BD0001=72
BD0003=120
BD0022=30
BD0005=9
BD0008=5

只26可用分了，不好意思。

[gsk09]

SQLServer2005

--> Test data : @SP_ProductP
declare @SP_ProductP table ([ProductP_ID] int,[ProductP_PID] int,[ProductP_Order] int)
insert into @SP_ProductP
select 11,19,1 union all
select 12,19,2 union all
select 15,19,3
--> Test data : @SP_ProductPItem
declare @SP_ProductPItem table ([ProductPI_PID] int,[ProductPI_Type] int,[ProductPI_MHNO] varchar(12),[ProductPI_UQty] int)
insert into @SP_ProductPItem
select 11,2,'BD0001',11 union all
select 11,2,'BD0003',20 union all
select 11,2,'BD0022',5 union all

select 12,2,'BD0005',3 union all
select 12,4,'H-HP-WH2980',2 union all
select 12,2,'BD0001',2 union all

select 15,4,'H2-HP-WH2980',3 union all
select 15,2,'BD0008',5

;with t as
(select sub.*,main.ProductP_Order,1 level
from (select top 1 * 
	from @SP_ProductP 
	where ProductP_PID = 19
	order by ProductP_Order desc
	) main
	inner join @SP_ProductPItem sub
		on main.ProductP_ID = sub.ProductPI_PID
union all
select
	 sub.ProductPI_PID
	,sub.ProductPI_Type
	,sub.ProductPI_MHNO
	,sub.ProductPI_UQty * t.ProductPI_UQty
	,main.ProductP_Order,level + 1
from t inner join @SP_ProductP main
		on t.ProductPI_Type = 4
		and main.ProductP_Order = (t.ProductP_Order - 1)
	inner join @SP_ProductPItem sub
		on sub.ProductPI_PID = main.ProductP_ID
)
select 
	 ProductPI_MHNO + '=' + ltrim(sum(ProductPI_UQty))
from t 
where t.ProductPI_Type = 2
group by ProductPI_MHNO
order by ProductPI_MHNO

-------------------------
BD0001=72
BD0003=120
BD0005=9
BD0008=5
BD0022=30

[wolftomb]

SQL2K里面不支持With 一般需要怎么处理?

[-狙击手-]

[Quote=引用 2 楼 wolftomb 的回复:]
SQL2K里面不支持With 一般需要怎么处理?
[/Quote]

用一个临时表
中间表

[appleller]

MARK

[wolftomb]

有没有人帮忙弄成SQL2K的，自己折腾了一晚上，没得到结果。先谢谢了。