22,206
社区成员
发帖
与我相关
我的任务
分享
-表结构
--库存表INV:
CREATE TABLE [dbo].[INV](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ClientName] [nvarchar](50) NULL,
[WareNO] [nvarchar](50) NULL,
[WareTime] [datetime] NULL,
[SkuCode] [nvarchar](50) NULL,
[SkuName] [nvarchar](80) NULL,
[PchNO] [nvarchar](50) NULL,
[INVSta] [nvarchar](50) NULL,
[Loca] [nvarchar](50) NULL,
[Qty] [float] NULL,
[Zqty] [float] NULL,
CONSTRAINT [PK_INV] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--出库表OutList
CREATE TABLE [dbo].[OutList](
[ID] [int] IDENTITY(1,1) NOT NULL,
[OutNO] [nvarchar](50) NULL,
[ClientName] [nvarchar](50) NULL,
[SkuCode] [nvarchar](50) NULL,
[SkuName] [nvarchar](80) NULL,
[PchNO] [nvarchar](50) NULL,
[INVSta] [nvarchar](50) NULL,
[Qty] [float] NULL,
CONSTRAINT [PK_OutList] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--库存表INV数据
insert INV(ClientName,WareNO,WareTime,SkuCode,SkuName,PchNO,INVSta,Loca,Qty,Zqty) values( N'万安食品', N'OP201408080025', '2014-07-23 14:41:59.000' , N'C4800201010' , N'丹麦蓝罐曲奇681克饼干1X6' , N'P20140727' , N'良品' , N'D541' , 10 , 0 )
insert INV(ClientName,WareNO,WareTime,SkuCode,SkuName,PchNO,INVSta,Loca,Qty,Zqty) values( N'万安食品', N'OP201408080025', '2014-07-23 14:41:59.000' , N'C4800201016' , N'丹麦蓝罐曲奇908克+90克原味2014MA佳节共享装1*6' , N'P20140521' , N'良品' , N'D311' , 10 , 0 )
insert INV(ClientName,WareNO,WareTime,SkuCode,SkuName,PchNO,INVSta,Loca,Qty,Zqty) values( N'万安食品', N'OP201408080025', '2014-07-23 14:41:59.000' , N'C2920105001' , N'家乐氏(蜜果脆圈圈250克+谷维滋30克)16纸盒装M' , N'P20140529' , N'良品' , N'D322' , 10 , 0 )
insert INV(ClientName,WareNO,WareTime,SkuCode,SkuName,PchNO,INVSta,Loca,Qty,Zqty) values( N'万安食品', N'OP201408080046', '2014-07-25 14:41:59.000' , N'C4800201010' , N'丹麦蓝罐曲奇681克饼干1X6' , N'P20140727' , N'良品' , N'D541' , 10 , 0 )
insert INV(ClientName,WareNO,WareTime,SkuCode,SkuName,PchNO,INVSta,Loca,Qty,Zqty) values( N'万安食品', N'OP201408080046', '2014-07-25 14:41:59.000' , N'C4800201016' , N'丹麦蓝罐曲奇908克+90克原味2014MA佳节共享装1*6' , N'P20140531' , N'良品' , N'D543' , 10 , 0 )
--出库表OutList 数据
insert OutList(ID,OutNO,ClientName,SkuCode,SkuName,PchNO,INVSta,Qty) values( 1 , N'OGR201408080001' , N'万安食品' , N'C4800201010' , N'丹麦蓝罐曲奇681克饼干1X6' , N'P20140727' , N'良品' , 7 )
insert OutList(ID,OutNO,ClientName,SkuCode,SkuName,PchNO,INVSta,Qty) values( 2 , N'OGR201408080001' , N'万安食品' , N'C4800201016' , N'丹麦蓝罐曲奇908克+90克原味2014MA佳节共享装1*6' , N'P20140521' , N'良品' , 1 )
--两表判断条件:ClientName,SkuCode,PchNO,INVSta,根据WareTime排序按先进出,OutList的QTY更新INV ZQty数量,得出如下结果
/*ID ClientName WareNO WareTime SkuCode SkuName PchNO INVSta Loca Qty Zqty
1 万安食品 OP201408080025 2014-7-23 14:41 C4800201010 丹麦蓝罐曲奇681克饼干1X6 P20140727 良品 D541 10 7
2 万安食品 OP201408080025 2014-7-23 14:41 C4800201016 丹麦蓝罐曲奇908克+90克原味2014MA佳节共享装1*6 P20140521 良品 D311 10 1
3 万安食品 OP201408080025 2014-7-23 14:41 C2920105001 家乐氏(蜜果脆圈圈250克+谷维滋30克)16纸盒装M P20140529 良品 D322 10 0
4 万安食品 OP201408080046 2014-7-25 14:41 C4800201010 丹麦蓝罐曲奇681克饼干1X6 P20140727 良品 D541 10 0
5 万安食品 OP201408080046 2014-7-25 14:41 C4800201016 丹麦蓝罐曲奇908克+90克原味2014MA佳节共享装1*6 P20140531 良品 D543 10 0
*/