开发工具:
文件大小: 4kb
下载次数: 0
上传时间: 2009-02-28
详细说明： USE AA if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmp_Last]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tmp_Last] CREATE TABLE [tmp_Last] ( [FEntryID] [int] NOT NULL , [FBillNo] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [FNumber] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [FQty] [decimal](18, 4) NOT NULL , [FBatchNo] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [FSPID] [int] NOT NULL ) ON [PRIMARY] GO if exists (select * from dbo.sysobjects where i d = object_id(N'[dbo].[tmp_Source]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tmp_Source] if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmp_Batch]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tmp_Batch] Select nOutBillSeq As FEntryID , cOutBillNo As FBillNo , FNumber , nQua As FQty , FStockPlaceID As FSPID Into tmp_Source From Czq092801 Select FNumber ,FBatchNo , FQty , FStockID , FStockPlaceID As FSPID Into tmp_Batch From Czq092802 ALTER TABLE tmp_Batch ADD FDetailID int IDENTITY (1, 1) NOT NULL go Update tmp_Batch Set FSPID = 464 where FNumber = 'E.A.AZ9719410054' Update tmp_Batch Set FSPID = 464 where FNumber = 'E.A.880410054' Update tmp_Batch Set FSPID = 465 Where fnumber = 'E.A.AZ9719410054' Update tmp_Source Set FSPID = t.FSPID From tmp_Batch t Where tmp_Source.FNumber = t.FNumber --以下是把批号拆分的游标 declare @EntryID int --定义变量行号 declare @BillNo varchar(100) --定义变量单据号 declare @Number varchar(100) --定义变量物料代码 declare @qty decimal(18,6) --定义变量数量 declare @SPID int --定义变量仓位代码 declare @BatchNo varchar(100) --定义变量批号 declare @DID int declare @BQty decimal(18,6) Declare subgroup Cursor For Select FEntryID, FBillNo , FNumber , FQty , FSPID From tmp_Source Open subgroup Fetch Next From subgroup Into @EntryID , @BillNo , @Number , @qty , @SPID While @@FETCH_STATUS = 0 Begin re: set @DID = 0 set @DID = (Select min(FDetailID) From tmp_Batch Where FNumber = @Number And FSPID = @SPID And FQty <> 0 ) set @BatchNo =(Select FBatchNo From tmp_Batch Where FDetailID = @DID ) Set @BQty =(Select FQty From tmp_Batch Where FDetailID = @DID ) print @BillNo print @EntryID if @BQty >= @Qty Begin Insert Into tmp_Last values (@EntryID , @BillNo , @Number , @Qty , @BatchNo , @SPID) Update tmp_Batch Set FQty = FQty - @Qty Where FDetailID = @DID print -1 Select -1 print @qty end Else Begin Insert Into tmp_Last values (@EntryID , @BillNo , @Number , @BQty , @BatchNo , @SPID) Update tmp_Batch Set FQty = FQty - @BQty Where FDetailID = @DID set @Qty = @Qty - @BQty print 1 print @qty goto re --返回到re： End print '888888' Fetch Next From subgroup Into @EntryID , @BillNo , @Number , @qty , @SPID End Close subgroup Deallocate subgroup --释放游标 ----------------------------------------------------------------- --更新行号 ALTER TABLE tmp_Last ADD FDetailID int IDENTITY (1, 1) NOT NULL ALTER TABLE tmp_Last ADD FNewEntryID int go declare @DID int declare @BillNo varchar(100) declare @i int Declare subgroup Cursor For Select Distinct FBillNo From tmp_Last Open subgroup Fetch Next From subgroup Into @BillNo While @@FETCH_STATUS = 0 Begin set @i =1 Declare detail Cursor For Select FDetailID From tmp_Last Where FBillNo = @BillNo Open detail Fetch Next From detail Into @DID --可以认为FDetailID的值附给了@DID While @@FETCH_STATUS = 0 Begin update tmp_Last Set FNewEntryID = @i Where FDetailID = @DID set @i = @i + 1 Fetch Next From detail Into @DID End Close detail Deallocate detail Fetch Next From subgroup Into @BillNo End Close subgroup Deallocate subgroup Select * From tmp_Last ------------------------------------------------------------------- /* Select FNumber,Sum(FQty) FQty From tmp_Last Group by FNumber Select FBillNo,Sum(FQty) FQty From tmp_Last Group by FBillNo Select FNumber,Sum(nQua) nQua From Czq092801 Group by FNumber Select cOutBillNo,Sum(nQua) nQua From Czq092801 Group by cOutBillNo */ ...展开收缩
(系统自动生成,下载前可以参看下载内容)
下载文件列表
相关说明
- 本站资源为会员上传分享交流与学习,如有侵犯您的权益,请联系我们删除.
- 本站是交换下载平台，提供交流渠道，下载内容来自于网络，除下载问题外，其它问题请自行百度。
- 本站已设置防盗链，请勿用迅雷、QQ旋风等多线程下载软件下载资源，下载后用WinRAR最新版进行解压.
- 如果您发现内容无法下载，请稍后再次尝试；或者到消费记录里找到下载记录反馈给我们.
- 下载后发现下载的内容跟说明不相乎，请到消费记录里找到下载记录反馈给我们，经确认后退回积分.
- 如下载前有疑问，可以通过点击"提供者"的名字，查看对方的联系方式，联系对方咨询.
