再请教 MSSQL2005 深度计算多版本bom单价

sufull88 2012-01-19 05:31:49
MSSQL2005 深度计算多版本bom单价 ---- 两种计算方法展开
表关系:goodsbom.billid=goodsbomdetail.billid 相同时表示同一层bom
主表goodsbom
单据ID,物料母件ID,版本号,母件数量
billid,materialid,bomno,quantity
1001 , A , AA , 1
1002 , B , BB , 1
1003 , F , FF , 1
1004 , A , AK , 1

从表goodsbomdetail
单据ID,明细序号, 物料子件ID,子件数量 ,子件版本号
billid,itemno,elemgid,quantity,bomno
1001 , 1 , B , 1 , BB
1001 , 2 , C , 1 , ' '
1001 , 3 , D , 1 , ' '
1002 , 1 , E , 1 , ' '
1002 , 2 , F , 2 , FF
1003 , 1 , G , 1 , ' '
1003 , 2 , H , 1 , ' '
1004 , 1 , J , 1 , ' '
1004 , 2 , K , 1 , ' '
物料单价表materialprice
materailid , price
A , 2
B , 1
C , 1
D , 1
E , 1
F , 1
G , 1
F , 1
J , 1
K , 1

要求结果如下: 以查询物料A为例, 如果查询物料B或物料F同理。 两种计算方法展开

1/计算最下层物料 (从最下层显示所需材料)
物料母件ID,母件版本号,母件数量 ,子件ID , 子件数量 ,子件序号,子件单价,子件金额
materialid ,bomno ,quantity ,elemgid,goodsbomdetailquantity,goodsbomdetailbomno ,materialprice ,materialamt
A , AA , 1 , C , 1 , ' ' , 1 , 1
A , AA , 1 , D , 1 , ' ' , 1 , 1
A , AA , 1 , E , 1 , ' ' , 1 , 1
A , AA , 1 , G , 2 , ' ' , 1 , 2 ----请注意 这里需要子件G为2个
A , AA , 1 , H , 2 , ' ' , 1 , 2 ----请注意 这里需要子件H为2个
A , AK , 1 , J , 1 , ' ' , 1 , 1
A , AK , 1 , K , 1 , ' ' , 1 , 1

2/计算下一层物料 (只计算查询层所在材料或半成品)
materialid ,bomno ,quantity ,elemgid,goodsbomdetailquantity,goodsbomdetailbomno ,materialprice ,materialamt
A , AA , 1 , B , 1 , BB , 1 , 5 ---注意 这里的单价为B下级材料求和
A , AA , 1 , C , 1 , ' ' , 1 , 1
A , AA , 1 , D , 1 , ' ' , 1 , 1
A , AK , 1 , J , 1 , ' ' , 1 , 1
A , AK , 1 , K , 1 , ' ' , 1 , 1

感谢各位的帮忙!
...全文
135 15 打赏 收藏 转发到动态 举报
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
Vidor 2012-02-09
  • 打赏
  • 举报
回复
2000就不可能用视图实现了,函数或存储过程都可以:

-- function f2
create function f2(@mid varchar(8))
returns @tb table(mid varchar(8),mbom varchar(8),mqty int,id varchar(8),qty int,bom varchar(8),price int,amt int)
as
begin

declare @level int
set @level = 0

declare @nest table (lev int/*层级深度递归必须*/, mid varchar(8),mbom varchar(8),mqty int,id varchar(8),qty int,bom varchar(8),id2 varchar(8),qty2 int,bom2 varchar(8))
insert into @nest select @level, a.materialid, a.bomno, a.quantity, b.elemgid, a.quantity*b.quantity, b.bomno, b.elemgid, a.quantity*b.quantity, b.bomno from goodsbom a, goodsbomdetail b where a.billid=b.billid and a.materialid=@mid
while @@rowcount>0
begin
set @level=@level+1
insert into @nest select @level, c.mid, c.mbom, c.mqty, c.id, c.qty, c.bom, b.elemgid, a.quantity*b.quantity*c.qty2, b.bomno from goodsbom a, goodsbomdetail b, @nest c where c.lev=@level-1 and a.billid=b.billid and a.bomno=c.bom2
end

insert into @tb select mid, mbom, mqty, id, qty, bom, max(b.price)price, sum(qty2*c.price)amt from @nest a, materialprice b, materialprice c where a.bom2='' and a.id=b.materailid and a.id2=c.materailid group by mid, mbom, mqty, id, qty, bom

return

end
go


-- test f2
select * from f2('A')


需求1自己按f2改写
Vidor 2012-02-09
  • 打赏
  • 举报
回复
-- 2:2000递归
declare @nest table (lev int/*层级深度递归必须*/, mid varchar(8),mbom varchar(8),mqty int,id varchar(8),qty int,bom varchar(8),id2 varchar(8),qty2 int,bom2 varchar(8))
declare @level int
set @level = 0 -- lev初始化
insert into @nest select @level, a.materialid, a.bomno, a.quantity, b.elemgid, a.quantity*b.quantity, b.bomno, b.elemgid, a.quantity*b.quantity, b.bomno from #goodsbom a, #goodsbomdetail b where a.billid=b.billid and a.materialid='A'
while @@rowcount>0
begin
set @level=@level+1
insert into @nest select @level, c.mid, c.mbom, c.mqty, c.id, c.qty, c.bom, b.elemgid, a.quantity*b.quantity*c.qty2, b.bomno from #goodsbom a, #goodsbomdetail b, @nest c where c.lev=@level-1 and a.billid=b.billid and a.bomno=c.bom2
end
select mid, mbom, mqty, id, qty, bom, max(b.price)price, sum(qty2*c.price)amt from @nest a, #materialprice b, #materialprice c where a.bom2='' and a.id=b.materailid and a.id2=c.materailid group by mid, mbom, mqty, id, qty, bom


自己复制#5的数据来测试了
勿勿 2012-02-09
  • 打赏
  • 举报
回复
[Quote=引用 11 楼 sufull88 的回复:]
引用 10 楼 szstephenzhou 的回复:
兼容级别 设置为80


现在都是用兼容级别 设置为80,因为原来的软件是用SQL2000平台开发的,我把兼容级别 设置为90时软件不能正常使用.
[/Quote]

sql 2000不支持 with cte 把他改成视图就好了。
sufull88 2012-02-09
  • 打赏
  • 举报
回复
[Quote=引用 10 楼 szstephenzhou 的回复:]
兼容级别 设置为80
[/Quote]

现在都是用兼容级别 设置为80,因为原来的软件是用SQL2000平台开发的,我把兼容级别 设置为90时软件不能正常使用.
sufull88 2012-02-09
  • 打赏
  • 举报
回复
感谢你们的热心帮助,谢谢!!
勿勿 2012-02-07
  • 打赏
  • 举报
回复
兼容级别 设置为80
Vidor 2012-02-07
  • 打赏
  • 举报
回复
改90模式:先在测试服务器测试
sufull88 2012-02-04
  • 打赏
  • 举报
回复
[Quote=引用 7 楼 vidor 的回复:]
引用 6 楼 sufull88 的回复:
谢谢你的回复,但这好像是静态的,如何可以动态的,转换成函数或视图?


这。。。不应该成为问题吧


SQL code
create view view1 as -- 1/
with cte(mid,mbom,mqty,id,qty,bom) as
(
select a.materialid, a.bomno, a.quant……
[/Quote]

实在太好了,这么详细,感谢!
我的数据文件是SQL2000的,安装了SQL2005,在2005环境下面运行你提供的脚本有出错提示,有什么办法可以在兼容80的模式下使用?


sufull88 2012-02-01
  • 打赏
  • 举报
回复
[Quote=引用 5 楼 vidor 的回复:]
SQL code
-- #goodsbom
if object_id('tempdb.dbo.#goodsbom') is not null drop table #goodsbom
create table #goodsbom(billid int, materialid varchar(8), bomno varchar(8), quantity int)
insert into #g……
[/Quote]

谢谢你的回复,但这好像是静态的,如何可以动态的,转换成函数或视图?
Vidor 2012-02-01
  • 打赏
  • 举报
回复
[Quote=引用 6 楼 sufull88 的回复:]
谢谢你的回复,但这好像是静态的,如何可以动态的,转换成函数或视图?
[/Quote]

这。。。不应该成为问题吧

create view view1 as -- 1/
with cte(mid,mbom,mqty,id,qty,bom) as
(
select a.materialid, a.bomno, a.quantity, b.elemgid, a.quantity*b.quantity, b.bomno from goodsbom a, goodsbomdetail b where a.billid=b.billid-- and a.materialid='A'
union all
select c.mid, c.mbom, c.mqty, b.elemgid, a.quantity*b.quantity*c.qty, b.bomno from goodsbom a, goodsbomdetail b, cte c where a.billid=b.billid and a.bomno=c.bom
)
select a.*, b.price, amt=a.qty*b.price from cte a, materialprice b where a.id=b.materailid and a.bom=''
go


create function function2(@mid varchar(100)) -- 2/
returns table return
with cte(mid,mbom,mqty,id,qty,bom,id2,qty2,bom2) as
(
select a.materialid, a.bomno, a.quantity, b.elemgid, a.quantity*b.quantity, b.bomno, b.elemgid, a.quantity*b.quantity, b.bomno from goodsbom a, goodsbomdetail b where a.billid=b.billid and a.materialid=@mid
union all
select c.mid, c.mbom, c.mqty, c.id, c.qty, c.bom, b.elemgid, a.quantity*b.quantity*c.qty2, b.bomno from goodsbom a, goodsbomdetail b, cte c where a.billid=b.billid and a.bomno=c.bom2
)
select mid, mbom, mqty, id, qty, bom, max(b.price)price, sum(qty2*c.price)amt from cte a, materialprice b, materialprice c where a.bom2='' and a.id=b.materailid and a.id2=c.materailid group by mid, mbom, mqty, id, qty, bom
go

-- test
select * from view1 where mid='A'
select * from function2('A')
shuohuameijiang 2012-01-31
  • 打赏
  • 举报
回复
不太明白LZ的意思
Vidor 2012-01-31
  • 打赏
  • 举报
回复
-- #goodsbom
if object_id('tempdb.dbo.#goodsbom') is not null drop table #goodsbom
create table #goodsbom(billid int, materialid varchar(8), bomno varchar(8), quantity int)
insert into #goodsbom
select 1001, 'A', 'AA', 1 union all
select 1002, 'B', 'BB', 1 union all
select 1003, 'F', 'FF', 1 union all
select 1004, 'A', 'AK', 1

-- #goodsbomdetail
if object_id('tempdb.dbo.#goodsbomdetail') is not null drop table #goodsbomdetail
create table #goodsbomdetail(billid int, itemno int, elemgid varchar(8), quantity int, bomno varchar(8))
insert into #goodsbomdetail
select 1001, 1, 'B', 1, 'BB' union all
select 1001, 2, 'C', 1, '' union all
select 1001, 3, 'D', 1, '' union all
select 1002, 1, 'E', 1, '' union all
select 1002, 2, 'F', 2, 'FF' union all
select 1003, 1, 'G', 1, '' union all
select 1003, 2, 'H', 1, '' union all
select 1004, 1, 'J', 1, '' union all
select 1004, 2, 'K', 1, ''

-- #materialprice
if object_id('tempdb.dbo.#materialprice') is not null drop table #materialprice
create table #materialprice(materailid varchar(8), price int)
insert into #materialprice
select 'A', 2 union all
select 'B', 1 union all
select 'C', 1 union all
select 'D', 1 union all
select 'E', 1 union all
select 'F', 1 union all
select 'G', 1 union all
select 'H', 1 union all
select 'J', 1 union all
select 'K', 1

-- 1
;with cte(mid,mbom,mqty,id,qty,bom) as
(
select a.materialid, a.bomno, a.quantity, b.elemgid, a.quantity*b.quantity, b.bomno from #goodsbom a, #goodsbomdetail b where a.billid=b.billid and a.materialid='A'
union all
select c.mid, c.mbom, c.mqty, b.elemgid, a.quantity*b.quantity*c.qty, b.bomno from #goodsbom a, #goodsbomdetail b, cte c where a.billid=b.billid and a.bomno=c.bom
)
select a.*, b.price, amt=a.qty*b.price from cte a, #materialprice b where a.id=b.materailid and a.bom=''
/*
mid mbom mqty id qty bom price amt
-------- -------- ----------- -------- ----------- -------- ----------- -----------
A AA 1 C 1 1 1
A AA 1 D 1 1 1
A AA 1 E 1 1 1
A AA 1 G 2 1 2
A AA 1 H 2 1 2
A AK 1 J 1 1 1
A AK 1 K 1 1 1
*/

-- 2
;with cte(mid,mbom,mqty,id,qty,bom,id2,qty2,bom2) as
(
select a.materialid, a.bomno, a.quantity, b.elemgid, a.quantity*b.quantity, b.bomno, b.elemgid, a.quantity*b.quantity, b.bomno from #goodsbom a, #goodsbomdetail b where a.billid=b.billid and a.materialid='A'
union all
select c.mid, c.mbom, c.mqty, c.id, c.qty, c.bom, b.elemgid, a.quantity*b.quantity*c.qty2, b.bomno from #goodsbom a, #goodsbomdetail b, cte c where a.billid=b.billid and a.bomno=c.bom2
)
select mid, mbom, mqty, id, qty, bom, max(b.price)price, sum(qty2*c.price)amt from cte a, #materialprice b, #materialprice c where a.bom2='' and a.id=b.materailid and a.id2=c.materailid group by mid, mbom, mqty, id, qty, bom
/*
mid mbom mqty id qty bom price amt
-------- -------- ----------- -------- ----------- -------- ----------- -----------
A AA 1 B 1 BB 1 5
A AA 1 C 1 1 1
A AA 1 D 1 1 1
A AK 1 J 1 1 1
A AK 1 K 1 1 1
*/
Vidor 2012-01-30
  • 打赏
  • 举报
回复
从去年问到今年还没解决,也算SQL板块比较另类的了,我闲得慌帮你看看。
sufull88 2012-01-30
  • 打赏
  • 举报
回复
各位大侠还在放假中.....
sufull88 2012-01-19
  • 打赏
  • 举报
回复
等待各位的热心帮忙中......

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧