SQL 库存计算问题

Question

别用cursor呀，太慢了。
你是月末一次计算吗？还是随时计算。

Answer 1

A库期初库存:
物料编号  库存数量 库存金额
M0001      0       0A库入库单:
单据编号  日期        物料编号  入库数量 入库金额 入库单价
R0001  2007-11-1   M0001    100      200     2.00
R0002  2007-11-5   M0001    50       125     2.50A库出库单:
单据编号  日期        物料编号  发出数量  发出成本 发出单价
P0001   2007-11-2  M0001     50       100      2.00 
P0002   2007-11-5  M0001     50       112.50   2.25    调拨单:
单据编号  日期        调出库  调入库   物料编号  调拨数量  调拨成本  单价
A0001   2007-11-6    A      B     M0001      10      22.50  2.25 
A0002   2007-11-7    B      A     M0001       5      10.5   2.10   (由B库计算得出拨出成本)计算过程如下:
单据编号   日期     物料编号  收发数量  收发金额   结存数量  结存金额  平均单价
                    M0001                         0        0         0   (期初库存)
R0001  2007-11-1  M0001     100       200    100       200     2.00
P0001  2007-11-2  M0001     -50      -100     50       100     2.00
R0002  2007-11-5  M0001      50       125    100       225     2.25
P0002  2007-11-5  M0001      50       112.5   50       112.5   2.25
A0001  2007-11-6  M0001     -10        22.5   40        90     2.25
A0002  2007-11-7  M0001       5        10.5   45       100.5   2.2333

Answer 2

看不懂這條，為什么同樣出庫，p0001就是-的，而這條沒-
P0002     2007-11-5     M0001    50    112.5   50   112.5       2.25 

Answer 3

A库期初库存: 
物料编号     库存数量   库存金额 
M0001             0               0 A库入库单: 
单据编号     日期                 物料编号     入库数量   入库金额   入库单价 
R0001     2007-11-1       M0001         100             200           2.00 
R0002     2007-11-5       M0001         50               125           2.50 A库出库单: 
单据编号     日期                 物料编号     发出数量     发出成本   发出单价 
P0001       2007-11-2     M0001           50               100             2.00   
P0002       2007-11-5     M0001           50               112.50       2.25         调拨单: 
单据编号     日期                 调出库     调入库       物料编号     调拨数量     调拨成本     单价 
A0001       2007-11-6         A             B           M0001             10             22.50     2.25   
A0002       2007-11-7         B             A           M0001               5             10.5       2.10       (由B库计算得出拨出成本) 计算过程如下: 
单据编号       日期           物料编号     收发数量     收发金额       结存数量     结存金额     平均单价 
                                        M0001                                                   0                 0                   0       (期初库存) 
R0001     2007-11-1     M0001           100               200         100               200           2.00 
P0001     2007-11-2     M0001           -50             -100           50               100           2.00 
R0002     2007-11-5     M0001             50               125         100               225           2.25 
P0002     2007-11-5     M0001            -50               -112.5       50               112.5       2.25 
A0001     2007-11-6     M0001           -10                 22.5       40                 90           2.25 
A0002     2007-11-7     M0001               5                 10.5       45               100.5       2.2333

Answer 4

--沒有看明白最后那個2.2333怎么算出來的。
--猜的算是總成本/總數量，不對就自己改改
create table A(number varchar(10), hub int,hub_money int)
insert into A select 'M0001',0,0create table A_in (order_no varchar(10),date datetime,number varchar(10),input int,input_money numeric(18,2),input_price numeric(18,2))
insert into A_in select 'R0001','2007-11-1','M0001',100,200,2.00
insert into A_in select 'R0002','2007-11-5','M0001',50,125,2.50create table A_out(order_no varchar(10),date datetime,number varchar(10),[output] int, out_money  numeric(18,2),out_price numeric(18,2))
insert into A_out select 'P0001','2007-11-2','M0001',50,100,2.00
insert into A_out select 'P0002','2007-11-5','M0001',50,112.50,2.25create table A_B (order_no varchar(10),date datetime, out_hub varchar(10), in_hub varchar(10), number varchar(10), quantity int, total_money numeric(18,2),price numeric(18,2))
insert into A_B select 'A0001','2007-11-6','A','B','M0001',10,22.5,2.25
insert into A_B select 'A0002','2007-11-7','B','A','M0001',5,10.5,2.10
select  order_no,date,number,in_quantity,in_money,hub_quantity,hub_money,convert(numeric(18,2),price) as price
into #
from
(
select  convert(varchar(10),'') as order_no ,null as [date] ,number, 0 as in_quantity,0 as in_money,0 as hub_quantity,convert(numeric(18,2),0) as hub_money,0 as price,tmp=1
from A
union all 
select order_no,date,number,input,input_money,0,0,input_price,tmp=2
from A_in
union all
select order_no,date,number,-[output],-out_money,0,0,out_price,tmp=3
from A_out
union all
select order_no,date,number,case when out_hub='A' then -quantity else quantity end,
                                            case when out_hub='A' then -total_money else total_money end,
                                            0,0,
                    case when out_hub='A' then price else (select sum(total_money)/sum(quantity) from A_B where number=x.number ) end,tmp=4
from A_B  x
) T
order by number,[date],tmpselect id=identity(int,1,1),* into #t from #update #t
set hub_quantity=(select sum(in_quantity) from #t a where a.number=#t.number and a.id<=#t.id),
      hub_money=abs(price*(select sum(in_quantity) from #t a where a.number=#t.number and a.id<=#t.id))
select order_no,date,number,in_quantity,in_money,hub_quantity,hub_money,price from #t order by id
/*
M0001 0 .00 0 .00 .00
R0001 2007-11-01 00:00:00.000 M0001 100 200.00 100 200.00 2.00
P0001 2007-11-02 00:00:00.000 M0001 -50 -100.00 50 100.00 2.00
R0002 2007-11-05 00:00:00.000 M0001 50 125.00 100 250.00 2.50
P0002 2007-11-05 00:00:00.000 M0001 -50 -112.50 50 112.50 2.25
A0001 2007-11-06 00:00:00.000 M0001 -10 -22.50 40 90.00 2.25
A0002 2007-11-07 00:00:00.000 M0001 5 10.50 45 99.00 2.20*/drop table #,#t
drop table A,A_in,A_out,A_B

Answer 5

Plywarcraft,谢谢你先!我看了上面的代码? 感觉和我的想法有点差距.
1.出库单的金额和单价需要计算求得,并不是已知.
2.调拨出库单据上的金额和单价要计算求出.
3.调拨入库单据上的金额和单价要求根据调拨出库来计算求得调拨日期时的金额和单价.
计算过程如下: 
单据编号   日期     物料编号 收发数量 收发金额 结存数量   结存金额  平均单价 
                     M0001                       0        0     0       (期初库存) 
R0001  2007-11-1  M0001     100     200     100      200     2.00 
P0001  2007-11-2  M0001     -50    -100      50      100     2.00 (发出金额(100)= 50 * 2.00  结存金额(100)=200-50*2.00)
R0002  2007-11-5  M0001      50     125     100      225     2.25 (平均单价(2.25)=(100 + 125)/(50+50))
P0002  2007-11-5  M0001     -50    -112.5    50      112.5   2.25 (发出金额(112.5)=50*2.25  结存金额(112.5) = 225-50*2.25)
A0001  2007-11-6  M0001     -10     -22.5    40       90     2.25 (发出金额(22.5)=10*2.25   结存金额(90)=112.5-22.5)
A0002  2007-11-7  M0001       5      10.5    45      100.5   2.2333(转入金额(10.5)是一个假设值,是通过计算B库得到, 库存金额(100.5)=90+10.5   单价(2.2333)=100.5/(40+5))解释一下转入单A0002的金额(10.5)的计算方法:用移动平均价对B库计算,到A0002时的转出成本就是此物料转入A库的成本.
(如果我们现在不是计算A库,而是计算B库.  那么A0001单据的转出的金额22.5就是B库计算时使用的转入成本)

Answer 6

sql查询题解倒数第三个例题.
见我blog.

Answer 7

-------------------------------------------------------------------------
-- 以下是面向过程的算法,速度太慢, 请各位高手能写出更高效的算法, 谢谢!
-------------------------------------------------------------------------
--建立期初库存表A
CREATE TABLE A (MtrNo char(10), Qty decimal(9,2), Amt money)
INSERT INTO A (MtrNo, Qty, Amt) VALUES ('M0001', 0, 0)--建立期初库存表B
CREATE TABLE B (MtrNo char(10), Qty decimal(9,2), Amt money)
INSERT INTO B (MtrNo, Qty, Amt) VALUES ('M0001', 50, 180)
--建立入库单表(a库)
CREATE TABLE A_in ([Id] int IDENTITY(1,1) NOT NULL, BillNo char(10), BillDate datetime, MtrNo char(10), InQty decimal(9,2), InAmt money, Price smallmoney)
INSERT INTO A_in (BillNo, BillDate, MtrNo, InQty, InAmt, Price) VALUES ('R0001', '2007-11-1', 'M0001', 100, 200, 2)
INSERT INTO A_in (BillNo, BillDate, MtrNo, InQty, InAmt, Price) VALUES ('R0002', '2007-11-5', 'M0001', 50, 125, 2.5)--建立出库单(A库)
CREATE TABLE A_out ([Id] int IDENTITY(1,1) NOT NULL, BillNo char(10), BillDate datetime, MtrNo char(10), OutQty decimal(9,2), OutAmt money, Price smallmoney)
INSERT INTO A_out (BillNo, BillDate, MtrNo, OutQty, OutAmt, Price) VALUES ('P0001', '2007-11-1', 'M0001', 50, 0, 0)
INSERT INTO A_out (BillNo, BillDate, MtrNo, OutQty, OutAmt, Price) VALUES ('P0002', '2007-11-5', 'M0001', 50, 0, 0)--建立调拨单
CREATE TABLE AB ([Id] int IDENTITY(1,1) NOT NULL, BillNo char(10), BillDate datetime, FromWh char(1), ToWh char(1), MtrNo char(10), MoveQty decimal(9,2), MoveAmt money, Price smallmoney)
INSERT INTO AB (BillNo, BillDate, FromWh, ToWh, MtrNo, MoveQty, MoveAmt, Price) VALUES ('A0001', '2007-11-6', 'A', 'B', 'M0001', 10, 0, 0)
INSERT INTO AB (BillNo, BillDate, FromWh, ToWh, MtrNo, MoveQty, MoveAmt, Price) VALUES ('A0002', '2007-11-7', 'B', 'A', 'M0001', 5, 0, 0)------------------------------------------------------------------------
--要求: 计算出发出金额/单价, 调拨出金额/单价, 调拨入金额单价, 结存数量和结存金额
------------------------------------------------------------------------
declare @curio cursor
declare @mtrno char(10), @qty decimal(9,2), @amt money, @price smallmoney
declare @id int, @billno char(10), @billdate datetime, @ioqty decimal(9,2), @ioamt money, @inx intcreate table #mx (mtrno char(10), billno char(10), billdate datetime, ioqty decimal(9,2), ioamt money, qty decimal(9,2), amt money, price smallmoney)--生成一个用于计算的临时表
set @curio = cursor for
select id, billno, billdate, mtrno, qty, amt, inx 
from 
(
 select id, billno, billdate, mtrno, inqty as qty, inamt as amt, price, inx = 1 from a_in
 union all 
 select id, billno, billdate, mtrno, outqty as qty, outamt as amt, price, inx = 3 from a_out
 union all
 select id, billno, billdate, mtrno, moveqty as qty, moveamt as amt, price, inx = 2 from ab where towh = 'A'
 union all
 select id, billno, billdate, mtrno, moveqty as qty, moveamt as amt, price, inx = 4 from ab where fromwh = 'A'
) t order by t.mtrno, t.billdate, t.inxselect @qty = qty, @amt = amt from A where mtrno = 'M0001'
if @qty = 0 set @price = 0 else set @price = @amt / @qtyinsert into #mx (mtrno, qty, amt, price) values ('M0001', @qty, @amt, @price)open @curio
fetch next from @curio into @id, @billno, @billdate, @mtrno, @ioqty, @ioamt, @inx
while @@fetch_status = 0
begin
  if @inx = 1  --入库单据
  begin
    select @qty = @qty + @ioqty, @amt = @amt + @ioamt
    if @qty = 0 set @price = 0 else set @price = @amt / @qty
  end        if @inx = 2 --调拨入单据
  begin
    select @ioamt = amt / qty * @ioqty from b where mtrno = 'M0001'
    select @qty = @qty + @ioqty, @amt = @amt + @ioamt
    if @qty = 0 set @price = 0 else set @price = @amt / @qty
    update ab set moveamt = @ioamt, price = @price where id = @id
  end  if @inx = '3' or @inx = '4'
  begin
    set @ioamt = @price * @ioqty
    select @qty = @qty - @ioqty, @amt = @amt - @ioamt
  end
  if @inx = '4'
    update ab set moveamt = @ioamt, price = @price where id = @id  if @inx = '3'
    update a_out set outamt = @ioamt, price = @price where id = @id  insert into #mx (mtrno, billno, billdate, ioqty, ioamt, qty, amt, price) values 
    ('M0001', @billno, @billdate, case when @inx = '3' or @inx = '4' then (-1) * @ioqty else @ioqty end,
                                  case when @inx = '3' or @inx = '4' then (-1) * @ioamt else @ioamt end, @qty, @amt, @price)  fetch next from @curio into @id, @billno, @billdate, @mtrno, @ioqty, @ioamt, @inx
end
close @curio
deallocate @curioselect * from #mx
select * from a_out
select * from abdrop table #mx
DROP TABLE A, B, A_out, A_in, AB