请教一个sql库存问题

Question

在SQL 2000中有药品库存表1
药品货号   药品名称   库存数量   批号
000001     青霉素      2         01
000001     青霉素      3         02
000001     青霉素      5         03
000001     青霉素      4         04
000001     青霉素      6         05
现在我我想要出库青霉素数量为17,要求按批号小的先出库,如出库数量17后表1的数据如下:
药品货号   药品名称   库存数量   批号
000001     青霉素      0         01
000001     青霉素      0         02
000001     青霉素      0         03
000001     青霉素      2         04
000001     青霉素      6         05怎样写SQL 出库代码????谢谢各位帮忙!!! 

Answer 1

http://blog.csdn.net/fredrickhu/archive/2009/09/20/4573392.aspx不是很会 给个例子看看 是PT哥写的 

Answer 2

/*---------------------------------
--  Author : htl258(Tony)
--  Date   : 2009-09-21 10:12:01
--  Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
Mar 29 2009 10:27:29 
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)---------------------------------*/
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([药品货号] NVARCHAR(10),[药品名称] NVARCHAR(10),[库存数量] INT,[批号] NVARCHAR(10))
INSERT [tb]
SELECT '000001',N'青霉素',2,'01' UNION ALL
SELECT '000001',N'青霉素',3,'02' UNION ALL
SELECT '000001',N'青霉素',5,'03' UNION ALL
SELECT '000001',N'青霉素',4,'04' UNION ALL
SELECT '000001',N'青霉素',6,'05'
GO
--SELECT * FROM [tb]-->SQL查询如下:
DECLARE @outqty INT
SET @outqty=17
--查询:
SELECT [药品货号],[药品名称],
库存数量=CASE WHEN (SELECT SUM([库存数量]) FROM tb WHERE [药品货号]=t.[药品货号] AND 批号<=t.批号)< @outqty
THEN 0 ELSE (SELECT SUM([库存数量]) FROM tb WHERE [药品货号]=t.[药品货号] AND 批号<=t.批号)-@outqty
END,
[批号]
FROM tb t --更新:
UPDATE t SET 
库存数量=CASE WHEN (SELECT SUM([库存数量]) FROM tb WHERE [药品货号]=t.[药品货号] AND 批号<=t.批号)< @outqty
THEN 0 ELSE (SELECT SUM([库存数量]) FROM tb WHERE [药品货号]=t.[药品货号] AND 批号<=t.批号)-@outqty
 END
FROM tb tSELECT * FROM tb
/*
药品货号       药品名称       库存数量        批号
---------- ---------- ----------- ----------
000001     青霉素        0           01
000001     青霉素        0           02
000001     青霉素        0           03
000001     青霉素        0           04
000001     青霉素        3           05(5 行受影响)
*/

Answer 3

用游标计算。只是业务处理，如果要得到数据，可以有一个出库的单据号，在得到单据号根据单据号进行查询就可以了。create procedure OutInventory
  @yphh varchar(20),@Qty int
as
  declare @tempQty int,@ph varchar(10)
  declare curOut cursor for 
   select 库存数量,批号 from 药品库存表 where 药品货号=@yphh order by 批号   open curOut
  fetch next from curOut into @tempQty int,@ph varchar(10)
  
  while @@fetch_status=0 begin
      if @tempQty>=@qty then begin
         insert into OutInventory(药品货号,批号,出库数量,....)
            values(@yphh,@ph,@qty)
         update 药品库存表 set 库存数量=库存数量-@qty where current of curOut
         break
      end else begin
         insert into OutInventory(药品货号,批号,出库数量,....)
            values(@yphh,@ph,@tempqty)
         delete from 库存数量 where current of curOut
         select @qty=@qty-@tempQty
      end
      fetch next from curOut into @tempQty int,@ph varchar(10)
  end  close curOut
  deallocate curOut   

Answer 4

ALTER                                       trigger tu_outbills on fast_tPT_OutBills for update
as
set nocount on
if not update(cbillstatus) returndeclare @cbillid char(20),@cbillstatusnew varchar(8),@cbillstatusold varchar(8),@ifsp int,@string varchar(30)
declare @dh char(2)select @cbillid=cbillid,@cbillstatusnew=cbillstatus from insertedif (@@rowcount<>1) 
begin
raiserror('不能审核多个单据',16,1)
rollback tran
return
endselect @cbillstatusold=cbillstatus from deleted
if  ((@cbillstatusnew='记账') and (@cbillstatusold='新单'))  begin
--select cBillID, iNo, cItemID, cBaseItemID, cBarCode, cName, cModel, cBrand, cMeasure, cBatchID, cVendorID, fUnitQty, fMvQty, fSdQty, fMvPrice, fSellPrice,'0000' as cdeptid into #ls3 from dbo.tPT_MvOutItems where 1>2

declare @ls3  TABLE  (
[cBillID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[iNo] [int] NOT NULL ,
[cItemID] [varchar] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[cBaseItemID] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[cBarCode] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[cName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[cModel] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[cBrand] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[cMeasure] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[cBatchID] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[cVendorID] [varchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[fUnitQty] [float] NOT NULL ,
[fMvQty] [float] NOT NULL ,
[fSdQty] [float] NOT NULL ,
[fMvPrice] [float] NOT NULL ,
[fSellPrice] [float] NOT NULL ,
        [cdeptid] [char] (4))
declare @hangbills TABLE  (
[cBillID] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[cBillStatus] [varchar] (8) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[cBillType] [varchar] (8) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[dMvDate] [datetime] NULL ,
[cHandBy] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[cEntehBy] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[cStoreID] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[cStore] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[iPrint] [int] NULL ,
[cMem] [text] COLLATE Chinese_PRC_CI_AS NULL ) 

declare @hangitems TABLE  (
[cBillID] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[iNo] [int] NOT NULL ,
[cItemID] [char] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[cBaseItemID] [char] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[cBarCode] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[cName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[cModel] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[cBrand] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[cMeasure] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[fUnitQty] [float] NULL ,
[fMvQty] [float] NULL ,
[fMvPrice] [float] NULL ,
[fSellPrice] [float] NULL ,
[ifsp] [int] NULL ,
                [cdeptid] [char] (4) null,
[clvid] [char] (6) null) 


declare @ls1 table(citemid char(6),fmvqty float,ifsp int)

declare @ls2 table(citemid char(6),fmvqty float,ifsp int)

insert into @ls1
select citemid,fmvqty,ifsp from fast_tPT_OutItems where cbillid=@cbillid and citemid=cbaseitemid

insert into @ls2
select citemid,fmvqty,ifsp from fast_tPT_OutItems where cbillid=@cbillid and citemid<>cbaseitemid

Answer 5

if(@@rowcount<>0) 
begin

declare @ci char(6),@cb char(6),@fq float,@fn float

declare pre cursor local for 
select citemid,fmvqty from @ls2

open pre

while(2>1)
begin
fetch next from pre into @ci,@fq
if (@@fetch_status<>0)  break
while (2>1)
begin
select @cb=cbaseitemid,@fn=funitqty from tco_items where citemid=@ci
if(@ci=@cb) break
set @ci=@cb
set @fq=@fq*@fn
update @ls2
set citemid=@ci,fmvqty=@fq
where current of pre
end
end
deallocate pre

-----------------------------------------------转换成箱码转换基本单位

insert into @ls1 
select * from @ls2
--------------------------------------------合并两个表
end

------------------------------------------------

declare rockon cursor local for
select citemid,ifsp,sum(fmvqty) from @ls1 group by citemid,ifsp order by citemid asc,ifsp asc 

-------------------------定义游标,根据citemid排序汇总数量
declare @citemid char(6),@fqty  float,@fbaseqty float,@cdeptid char(4)

declare @iNo int, @cBaseItemID char(6), @cBarCode char(20),
 @cName varchar(100), @cModel varchar(100), @cBrand varchar(100), @cMeasure varchar(100), @cBatchID char(30), @cVendorID char(6),
 @fUnitQty float, @fMvQty float, @fSdQty float, @fMvPrice float, @fSellPrice float,@fbatchqty float,@faverprice float
-------------------------定义变量

-------------------------空单据号
open rockon
-------------------------打开游标
set @ino=1

while (2>1)
begin --1
fetch next from rockon into @citemid,@ifsp,@fqty

if (@@fetch_status<>0)  break

select @cbaseitemid=cbaseitemid,@cbarcode=cbarcode,@cdeptid=cdeptid,
@cname=cname,@cmodel=cmodel,@cbrand=cbrand,@cmeasure=cmeasure,@funitqty=funitqty,@fsdqty=0,
@fsellprice=fsellprice
from tco_items where citemid=@citemid

if(@@rowcount<>1) 
begin
set @string='编码不存在:'+@citemid
raiserror(@string,16,1)
rollback tran
return
end

if (@ifsp=0) 
begin
declare cc cursor local for 
select cbatchid,cvendorid,facceptqty-fretqty-fsellqty-fotherqty-fdamage,faverprice from dbo.tCO_Itembatch where citemid=@citemid and
facceptqty-fretqty-fsellqty-fotherqty-fdamage>0
order by cbatchid asc
end 
-------------------选择批次方式
else
begin
declare cc cursor local for 
select cbatchid,cvendorid,facceptqty-fretqty-fsellqty-fotherqty-fdamage,faverprice from dbo.tCO_Itembatch where citemid=@citemid and
facceptqty-fretqty-fsellqty-fotherqty-fdamage>0
order by cbatchid asc
end 

-------------------------选批次游标,先进先出排序

Answer 6

open cc

while (2>1)

begin
fetch next from cc into @cbatchid,@cvendorid,@fbatchqty,@faverprice
if (@@fetch_status<>0)  
begin
if (@fqty<=0) goto end1
select @cname=cname,
@cmodel=cmodel,@cmeasure=cmeasure,
@funitqty=funitqty,@fmvprice=fnowinprice,
@fsellprice=fsellprice,@cbaseitemid=cbaseitemid,
@cbrand=cbrand
from tco_items where citemid=@citemid

if not exists (select 1 from @hangbills where right(cBillID,18)=right(@cBillID,18))
begin
insert into @hangbills(cBillID, cBillStatus, cBillType, dMvDate, cHandBy, cEntehBy, cStoreID, cStore, iPrint, cMem)
select 'HG'+right(cBillID,18), cBillStatus, cBillType, dMvDate, cHandBy, cEntehBy, cStoreID, cStore, iPrint, cMem 
from dbo.fast_tPT_OutBills
where cbillid=@cbillid
end


insert into @hangitems(cBillID, iNo, cItemID, cBaseItemID, cBarCode, cName, cModel, cBrand, cMeasure, fUnitQty, fMvQty, fMvPrice, fSellPrice, ifsp)
select 'HG'+right(@cBillID,18),isnull(max(ino),0)+1, @citemid,@cbaseitemid, @cbarcode, @cname, @cmodel, @cbrand,@cmeasure, @funitqty, @fqty, @fmvprice, @fsellprice,@ifsp
from @hangitems where cbillid='HG'+right(@cBillID,18)

----------------------------没有批次的商品编码和数量
goto end1
end

if (@fbatchqty>@fqty)
----------------------------如果一个批次数量大于调拨数量
begin
set @fmvqty=convert(numeric(10,0),(@fqty-0.5))
set @fmvprice=@faverprice

insert into @ls3(cBillID, iNo, cItemID, cBaseItemID, cBarCode, cName, cModel, cBrand, cMeasure, cBatchID, cVendorID, fUnitQty, fMvQty, fSdQty, fMvPrice, fSellPrice,cdeptid)
values(@cBillID, @iNo, @cItemID, @cBaseItemID, @cBarCode, @cName, @cModel, @cBrand, @cMeasure, @cBatchID, @cVendorID, @fUnitQty, @fMvQty,0, @fMvPrice, @fSellPrice,@cdeptid)

set @ino=@ino+1
set @fqty=@fqty-@fmvqty

end
else
begin
-----------------------------批次数量不够一次调拨
if(@fbatchqty<1 and @fbatchqty>0) break
set @fmvqty=convert(numeric(10,0),(@fbatchqty-0.5))
set @fmvprice=@faverprice
-----------------------------整个批次全部调出,取进价
insert into @ls3(cBillID, iNo, cItemID, cBaseItemID, cBarCode, cName, cModel, cBrand, cMeasure, cBatchID, cVendorID, fUnitQty, fMvQty, fSdQty, fMvPrice, fSellPrice,cdeptid)
values(@cBillID, @iNo, @cItemID, @cBaseItemID, @cBarCode, @cName, @cModel, @cBrand, @cMeasure, @cBatchID, @cVendorID, @fUnitQty, @fMvQty,0, @fMvPrice, @fSellPrice,@cdeptid)
set @ino=@ino+1
set @fqty=@fqty-@fmvqty
-----------------------------序号自动增加,需调拨数量减批次数量
end

if @fqty<1 
end1: break
-------------------------需调出数量0的时候结束
end --2
deallocate cc
end --1
deallocate rockon

if exists(select 1 from 
(select citemid,sum(fmvqty) as fmvqty from
(select citemid,fmvqty from @ls1 union all
select citemid,-fmvqty from @ls3 union all
select citemid,-fmvqty from @hangitems) a group by citemid) a where fmvqty<>0)
begin
raiserror('未知错误,请重新审核单据',16,1)
rollback tran
--select * into ls1 from @ls1
--select * into ls2 from @ls2
--select * into ls3 from @ls3 
--select * into hangitems from @hangitems
return
end
else

Answer 7

begin declare @nowstoreid char(4),@tostoreid char(4)
select @nowstoreid=cvaule from LCTCSMDB.dbo.tMG_Parameter where cid='PUB01'
select @tostoreid=cstoreid from  dbo.fast_tPT_OutBills  where cbillid=@cbillid
if (@nowstoreid=@tostoreid) 
begin
raiserror('调出和调拨不能是同一个店号',16,1)
rollback tran
end
else
begin declare @insertdeptid char(4),@insertbillid char(20)

declare deptidup cursor local for 
select cdeptid from @ls3 group by cdeptid order by cdeptid asc

open deptidup

while(2>1)
begin
fetch next from deptidup into @insertdeptid
if(@@fetch_status<>0) break
set @dh=(case when @nowstoreid<>'0002' or @tostoreid<>'0002' then 'DB' else 'DC' end)
select  @insertbillid=dbo.tPT_MvOutBills_createbill(@dh,getdate(),1)
  print 'e'

update @ls3
set cbillid=@insertbillid
where cdeptid=@insertdeptid insert into dbo.tPT_MvOutBills(cBillID, cBillStatus, cBillType, dMvDate, cHandBy, cEntehBy, cStoreID, cStore, cDeptID, cDept, cWhsID, iPrint)
select @insertbillid,'新单',
case when @nowstoreid<>'0002' and @tostoreid<>'0002' then '调拨出库' 
when @nowstoreid<>'0002' and @tostoreid='0002' then '配退出库' 
else '配送出库' end
,a.dmvdate, a.cHandBy, a.cEntehBy, a.cStoreID,b.cStore,@insertdeptid,c.cdept,'0001',0 
from dbo.fast_tPT_OutBills a,lctcsmdb.dbo.tFD_Store b,dbo.tCO_Dept c
where a.cbillid=@cbillid and a.cstoreid=b.cstoreid and c.cdeptid=@insertdeptid insert into dbo.fast_tPT_LayOutBills
select @cbillid,@insertbillid,'出库',@insertdeptid

end deallocate deptidup
insert into dbo.fast_tPT_HangOutBills
select * from @hangbills
insert into dbo.fast_tPT_HangOutItems
select * from @hangitems

insert into dbo.tPT_MvOutItems(cBillID, iNo, cItemID, cBaseItemID, cBarCode, cName, cModel, cBrand, cMeasure, cBatchID, cVendorID, fUnitQty, fMvQty, fSdQty, fMvPrice, fSellPrice, fInAmt, fTZSellPrice)
select cBillID, iNo, cItemID, cBaseItemID, cBarCode, cName, cModel, cBrand, cMeasure, cBatchID, cVendorID, fUnitQty, fMvQty, 0, fMvPrice, fSellPrice,fmvqty*fmvprice, 0 from @ls3
--select * into sw from @hangbills
end
end
if(@@error<>0) 
begin rollback tran end
end

Answer 8

--> 测试数据: @t
declare @t table (药品货号 varchar(6),药品名称 varchar(6),库存数量 int,批号 varchar(2))
insert into @t
select '000001','青霉素',2,'01' union all
select '000001','青霉素',3,'02' union all
select '000001','青霉素',5,'03' union all
select '000001','青霉素',4,'04' union all
select '000001','青霉素',6,'05'
declare @out int
set @out = 17update b 
set 库存数量 =case when isnull(a.新库存数量,0) > a.库存数量 then a.库存数量 else isnull(a.新库存数量,0) end
from @t b  inner join
 (select  a.* 
,新库存数量  = (select sum(库存数量) from @t where 批号 <= a.批号 having sum(库存数量) > @out) 
-@out
from @t a) a
on b.批号 = a.批号select * from @t
药品货号   药品名称   库存数量        批号   
------ ------ ----------- ---- 
000001 青霉素    0           01
000001 青霉素    0           02
000001 青霉素    0           03
000001 青霉素    0           04
000001 青霉素    3           05（所影响的行数为 5 行）

Answer 9

[code=SQL]没有这么麻烦吧！SELECT [药品货号],[药品名称],
    库存数量=CASE WHEN (SELECT SUM([库存数量]) FROM tb WHERE [药品货号]=t.[药品货号] AND 批号<=t.批号)< @outqty
                    THEN 0 ELSE (SELECT SUM([库存数量]) FROM tb WHERE [药品货号]=t.[药品货号] AND 批号<=t.批号)-@outqty
            END,
    [批号]
FROM tb t [/code]这样就行了，5楼很好！