htl258 Tony兄有物料清单再请教!

Question

BOM能不能一次查询所有货品的成本单价？？我自己试改了一下，发现太慢了，有没有办法再帮个忙分别改为函数和存储过程去一次性查询所有货品的成本单价？谢谢！
http://topic.csdn.net/u/20090828/20/623de9a7-b417-4af5-adff-b1d19f28172d.html?seed=1991549658&r=59413960#r_59413960

Answer 1

能不能设置成函数FN_BOMCOST(),我查询时select * from FN_BOMCOST  或
select * from FN_BOMCOST where mgoodsid='A001'  

Answer 2

TONY哥这几天好像比较忙呀！怎么没有上来的

Answer 3

http://blog.csdn.net/zjcxc/category/125593.aspx看看老大的

Answer 4

刚看了一下，我在19楼给了一个方法的啊，
虽然样子很丑，可是很好用，主要是展BOM的速度很快。特殊需求稍改下就可以了

Answer 5

不好意思,这两天比较忙,都没时间上来,涉及到BOM的,量大效率都不高,关于展开所有BOM成本,参考以下代码:--> 生成测试数据表:BOMIf not object_id('[BOM]') is null
    Drop table [BOM]
Go
Create table [BOM]([BILLID] int,[MGOODSID] nvarchar(4),[MQTY] decimal(18,2),[DGOODSID] nvarchar(4),[ITEMNO] decimal(18,2),[DQTY] decimal(18,2),[DPRICE] decimal(18,2)) 
Insert BOM 
Select 584,'A001',100.00,'B001',1.00,100.00,1 union all 
Select 584,'A001',100.00,'C001',2,100,1.00 union all 
Select 584,'A001',100.00,'D001',3,100,1 union all 
Select 584,'A001',100.00,'B001',1.00,40.00,1 union all --增加一列
Select 588,'B001',100.00,'E001',1,100,1 union all 
Select 588,'B001',100.00,'F001',2,100,1 union all 
Select 560,'F001',100.00,'G001',1,100,1 union all 
Select 560,'F001',100.00,'J001',2,100,1 Go 
--Select * from BOM-->SQL查询如下:If not object_id('[Fn_BOM]') is null
    Drop function [Fn_BOM]
Go
Create Function Fn_BOM(@MGoodsID varchar(20))
returns @t table(MGOODSID varchar(20),MQTY dec(18,2),DGOODSID varchar(20),ITEMNO dec(18,2),DQTY dec(18,2),DPRICE dec(18,2),DAMT dec(18,2),lvl int)
as 
begin
    declare @lvl int
    set @lvl=0
    IF @MGoodsID IS NOT NULL
        insert @t select MGOODSID,MQTY,DGOODSID,ITEMNO,DQTY,DPRICE,NULL,@lvl from BOM where MGoodsID=@MGoodsID 
    ELSE 
        INSERT @t SELECT MGOODSID,MQTY,DGOODSID,ITEMNO,DQTY,DPRICE,NULL,@lvl from BOM 
        --WHERE EXISTS (SELECT 1 FROM BOM WHERE MGoodsID=b.MGoodsID)
    while @@rowcount>0
        begin
            set @lvl=@lvl+1
            insert @t 
            select a.MGOODSID,a.MQTY,a.DGOODSID,a.ITEMNO,a.DQTY,a.DPRICE,a.DQTY*a.DPRICE,@lvl 
            from BOM a,@t b
            where a.MGoodsID=b.DGoodsID 
                and b.lvl=@lvl-1
        end
    declare @i int
    select @i=max(lvl) from @t t where exists(select 1 from @t where t.dgoodsid=mgoodsid)
    while @i>=0
        begin
            update a set 
                dprice=(select sum(damt) from @t where a.DGOODSID=MGOODSID)/(select sum(mqty) from @t where DGOODSID=a.DGOODSID),
                damt=(select sum(damt) from @t where a.DGOODSID=MGOODSID)/(select count(1) from @t where DGOODSID=a.DGOODSID)
            from @t a
            where lvl=@i
                and exists(select 1 from @t where a.dgoodsid=mgoodsid)
            set @i=@i-1
        end
    update @t set DAMT=DQTY*DPRICE where DAMT is null
    return
end
go
--select * from fn_bom(null) 
--
If not object_id('[FN_BOMCOST]') is null
    Drop FUNCTION [FN_BOMCOST]
Go
Create FUNCTION [FN_BOMCOST](@GOODSID varchar(20))
returns @t TABLE
(
MGOODSID nvarchar(10),
MQTY DEC(18,2),
DGOODSID nvarchar(10),
DQTY DEC(18,2),
DPRICE DEC(18,2),
DAMT DEC(18,2)
)
as
BEGIN
IF @GOODSID IS NOT NULL
BEGIN
INSERT @t 
select MGOODSID,MQTY,DGOODSID ,DQTY,DPRICE,DAMT
from fn_bom(@GOODSID) 
where MGOODSID =@GOODSID
END
ELSE 
BEGIN 
DECLARE C CURSOR FOR SELECT DISTINCT MGOODSID FROM BOM
OPEN C
FETCH NEXT FROM C INTO @GOODSID
WHILE @@FETCH_STATUS=0
BEGIN
INSERT @t 
select MGOODSID,MQTY,DGOODSID ,DQTY,DPRICE,DAMT
from fn_bom(@GOODSID) 
where MGOODSID =@GOODSID

FETCH NEXT FROM C INTO @GOODSID
END
CLOSE C
DEALLOCATE C
END
RETURN
END
GOselect * from [FN_BOMCOST]('A001')
/*
MGOODSID MQTY DGOODSID DQTY DPRICE DAMT
A001 100.00 B001 100.00 3.00 300.00
A001 100.00 C001 100.00 1.00 100.00
A001 100.00 D001 100.00 1.00 100.00
A001 100.00 B001 40.00 3.00 300.00
*/
select * from [FN_BOMCOST](null)
/*
MGOODSID MQTY DGOODSID DQTY DPRICE DAMT
A001 100.00 B001 100.00 3.00 300.00
A001 100.00 C001 100.00 1.00 100.00
A001 100.00 D001 100.00 1.00 100.00
A001 100.00 B001 40.00 3.00 300.00
B001 100.00 E001 100.00 1.00 100.00
B001 100.00 F001 100.00 2.00 200.00
F001 100.00 G001 100.00 1.00 100.00
F001 100.00 J001 100.00 1.00 100.00
*/

Answer 6

谢谢回复,
select * from [FN_BOMCOST](null)
上面的函数能不能改为 select * from [FN_BOMCOST] 不要任何参数效率方面不知道怎么样,
如果需要过滤条件自己加WHERE就行了。没办法原来的软件需要用函数才能无缝链接，
否则要自己做个外挂程序。

Answer 7

If not object_id('[FN_BOMCOST]') is null
    Drop FUNCTION [FN_BOMCOST]
Go
Create FUNCTION [FN_BOMCOST]()
returns @t TABLE
(
    MGOODSID nvarchar(10),
    MQTY DEC(18,2),
    DGOODSID nvarchar(10),
    DQTY DEC(18,2),
    DPRICE DEC(18,2),
    DAMT DEC(18,2)
)
as
BEGIN
    DECLARE @GOODSID varchar(20)
    BEGIN 
        DECLARE C CURSOR FOR SELECT DISTINCT MGOODSID FROM BOM
        OPEN C
        FETCH NEXT FROM C INTO @GOODSID
        WHILE @@FETCH_STATUS=0
        BEGIN
            INSERT @t 
            select MGOODSID,MQTY,DGOODSID ,DQTY,DPRICE,DAMT
            from fn_bom(@GOODSID) 
            where MGOODSID =@GOODSID
                
            FETCH NEXT FROM C INTO @GOODSID    
        END
        CLOSE C
        DEALLOCATE C
    END
    RETURN
END
GO
不要任何参数就这样.
调用:
select * from [FN_BOMCOST]()

Answer 8

上述function 調用的結果只有1個，
程序死翹翹

Answer 9

Tony 兄
执行如下语句，查询结果需时30秒左右。
select * from [FN_BOMCOST]() where mgoodsid='av116'

Answer 10

定期更新这张表，这个要从BOM最下阶的开始算起，算完一阶再完一阶呀！