说明:
根据"发货表"的数据更新"库存表"的"占用数量",条件:相同商品编号,相同的仓库.请SQL语句,谢谢!
- SQL code
/*发货表 自增主键 发货单号 服务商 商品编号 商品名称 仓库 数量ID LoadTranNo LoadCarriers sku SKUNAME Loadkz qty117 H120914000158 深圳市恒7 800542 A2r 503 10118 H120914000158 深圳市恒7 800541 Cisr 503 6119 H120914000178 深圳市恒7 105324 sarnl 503 3120 H120914000178 深圳市恒7 800542 A2r 503 2121 H120914000178 深圳市恒7 714339 XD2 503 7库存表 自增主键 商品编号 批号 库位 仓库 商品数量 占用数量 留用 日期ID Sku BatchNo kuwei Loadkz Qty Qty1 Inqty3 Drdate313 800542 120724809 56112A 503 5 NULL 2012-9-18 15:17314 800542 120724809 56112B 503 6 NULL 2012-9-18 15:17315 800542 120911809 56103B 503 14 NULL 2012-9-18 15:17316 800541 120726801 56F07A 503 1 NULL 2012-9-18 15:17317 800541 120726801 56H10A 503 7 NULL 2012-9-18 15:17318 714339 120726801 56O05A 503 13 NULL 2012-9-18 15:17319 105324 120726801 56O07A 503 1 NULL 2012-9-18 15:17320 105324 120726801 56O07A 503 2 NULL 2012-9-18 15:17结果(更新qty1(占用数量)之后得出的结果) 自增主键 商品编号 批号 库位 仓库 商品数量 占用数量 留用 日期ID Sku BatchNo kuwei Loadkz Qty Qty1 Inqty3 Drdate313 800542 120724809 56112A 503 5 5 NULL 2012-9-18 15:17314 800542 120724809 56112B 503 6 6 NULL 2012-9-18 15:17315 800542 120911809 56103B 503 14 1 NULL 2012-9-18 15:17316 800541 120726801 56F07A 503 1 1 NULL 2012-9-18 15:17317 800541 120726801 56H10A 503 7 5 NULL 2012-9-18 15:17318 714339 120726801 56O05A 503 13 6 NULL 2012-9-18 15:17319 105324 120726801 56O07A 503 1 1 NULL 2012-9-18 15:17320 105324 120726801 56O07A 503 2 2 NULL 2012-9-18 15:17最后结果*/
现在只能发100分的贴子,问题解决在发100分,谢谢!
------解决方案--------------------
- SQL code
--> 测试数据：[发货表]IF OBJECT_ID('[发货表]') IS NOT NULL DROP TABLE [发货表]GO CREATE TABLE [发货表]([ID] INT,[LoadTranNo] VARCHAR(13),[LoadCarriers] VARCHAR(9),[sku] INT,[SKUNAME] VARCHAR(5),[Loadkz] INT,[qty] INT)INSERT [发货表]SELECT 117,'H120914000158','深圳市恒7',800542,'A2r',503,10 UNION ALLSELECT 118,'H120914000158','深圳市恒7',800541,'Cisr',503,6 UNION ALLSELECT 119,'H120914000178','深圳市恒7',105324,'sarnl',503,3 UNION ALLSELECT 120,'H120914000178','深圳市恒7',800542,'A2r',503,2 UNION ALLSELECT 121,'H120914000178','深圳市恒7',714339,'XD2',503,7--> 测试数据：[库存表]IF OBJECT_ID('[库存表]') IS NOT NULL DROP TABLE [库存表]GO CREATE TABLE [库存表]([ID] INT,[Sku] INT,[BatchNo] INT,[kuwei] VARCHAR(6),[Loadkz] INT,[Qty] INT,[Qty1] INT,[Inqty3] INT,[Drdate] DATETIME)INSERT [库存表]SELECT 313,800542,120724809,'56112A',503,5,NULL,NULL,'2012-9-18 15:17' UNION ALLSELECT 314,800542,120724809,'56112B',503,6,NULL,NULL,'2012-9-18 15:17' UNION ALLSELECT 315,800542,120911809,'56103B',503,14,NULL,NULL,'2012-9-18 15:17' UNION ALLSELECT 316,800541,120726801,'56F07A',503,1,NULL,NULL,'2012-9-18 15:17' UNION ALLSELECT 317,800541,120726801,'56H10A',503,7,NULL,NULL,'2012-9-18 15:17' UNION ALLSELECT 318,714339,120726801,'56O05A',503,13,NULL,NULL,'2012-9-18 15:17' UNION ALLSELECT 319,105324,120726801,'56O07A',503,1,NULL,NULL,'2012-9-18 15:17' UNION ALLSELECT 320,105324,120726801,'56O07A',503,2,NULL,NULL,'2012-9-18 15:17'--------------开始查询--------------------------UPDATE b SET b.qty1=case when b.[出货合计] <a.[发货合计] then b.[出货合计] else a.[发货合计] end - case when b.[出货合计]-b.[Qty] <a.[发货合计]-a.[发货合计] then a.[发货合计]-a.[发货合计] else b.[出货合计]-b.[Qty] endfrom ( SELECT sku,Loadkz,SUM(qty) AS [发货合计] FROM [发货表] GROUP BY sku,Loadkz)a join ( select*,[出货合计]=(select sum([Qty]) from [库存表] where [sku]=b.[sku] AND [Loadkz]=b.[Loadkz] and [ID]<= b.[ID]) from [库存表] b ) b on a.[sku]=b.[sku] AND a.[Loadkz]=b.[Loadkz] SELECT * FROM [库存表]----------------结果----------------------------/* ID Sku BatchNo kuwei Loadkz Qty Qty1 Inqty3 Drdate313 800542 120724809 56112A 503 5 5 NULL 2012-09-18 15:17:00.000314 800542 120724809 56112B 503 6 6 NULL 2012-09-18 15:17:00.000315 800542 120911809 56103B 503 14 1 NULL 2012-09-18 15:17:00.000316 800541 120726801 56F07A 503 1 1 NULL 2012-09-18 15:17:00.000317 800541 120726801 56H10A 503 7 5 NULL 2012-09-18 15:17:00.000318 714339 120726801 56O05A 503 13 7 NULL 2012-09-18 15:17:00.000319 105324 120726801 56O07A 503 1 1 NULL 2012-09-18 15:17:00.000320 105324 120726801 56O07A 503 2 2 NULL 2012-09-18 15:17:00.000*/