求UPDATE语句.在线等,谢谢

bitls 2012-03-13 03:27:17 


--库存表

/* 

TB1

SKU	BatchNo 	Qty	OrdQty	Volume

180050	DY101110	1	1	0.0351

180050	DY111117	1	1	0.075

180050	DY111108	18	9	2.5

180050	DY111109	7	4	1.40832

*/

TB2

--发货表

/*

SKU	Qty

180050	20

*/

--更新后库存表

/*

SKU	BatchNo 	Qty	OrdQty	Volume

180050	DY101110	0	1	0.0351

180050	DY111108	0	9	2.5

180050	DY111109	6	4	1.40832

180050	DY111117	1	1	0.075

*/

--求一更新语句,BatchNo字段升序排序后,UPDATE->QTY字段

--半把操作的库存表的字段插入后TB3

/*

TB3

SKU	BatchNo 	Qty	OrdQty	Volume

180050	DY101110	1	1	0.0351

180050	DY111108	18	9	2.5

180050	DY111109	1	4	1.40832

*/


在线等,解决后,马上结贴!
...全文
374 38 打赏 收藏 转发到动态 举报
写回复
用AI写文章
38 条回复
切换为时间正序
请发表友善的回复…
发表回复
octwind 2012-03-13
  • 打赏
  • 举报
 回复 


declare @cnt int, @tmp int

set @cnt = 0

set @tmp = 0

declare @TB1 table 

(SKU int,BatchNo varchar(8),Qty int,OrdQty int,Volume numeric(6,5))

insert into @TB1

select 180050,'DY101110',1,1,0.0351 union all

select 180050,'DY111117',1,1,0.075 union all

select 180050,'DY111108',18,9,2.5 union all

select 180050,'DY111109',7,4,1.40832





declare @TB2 table

(sku int,qty int)

insert into @TB2

select 180050,22 





update t1 set @cnt = @cnt + t3.Qty, @tmp = t2.qty - @cnt, t1.Qty = case when @tmp >= 0 then 0 else  -@tmp end  

from  (select top 100 percent * from @TB1 order by BatchNo) t3 inner join @TB1 t1 on t1.BatchNo = t3.BatchNo inner join @TB2 t2 on t3.SKU = t2.sku



select * from @TB1
黄_瓜 2012-03-13
  • 打赏
  • 举报
 回复
[Quote=引用 36 楼 beirut 的回复:]

擦.............刚忙了一会
等写出来，就结贴了

看来偶果然不是大牛


SQL code
--> 测试数据：[TB1]
if object_id('[TB1]') is not null drop table [TB1]
go
create table [TB1]([SKU] int,[BatchNo] varchar(8),[Qty] int,[OrdQty] i……
[/Quote]

认真看了一遍，貌似我写的不比大牛们差
黄_瓜 2012-03-13
  • 打赏
  • 举报
 回复
擦.............刚忙了一会
等写出来，就结贴了

看来偶果然不是大牛


--> 测试数据：[TB1]

if object_id('[TB1]') is not null drop table [TB1]

go 

create table [TB1]([SKU] int,[BatchNo] varchar(8),[Qty] int,[OrdQty] int,[Volume] numeric(6,5))

insert [TB1]

select 180050,'DY101110',1,1,0.0351 union all

select 180050,'DY111117',1,1,0.075 union all

select 180050,'DY111108',18,9,2.5 union all

select 180050,'DY111109',7,4,1.40832

--------------开始查询--------------------------

--> 测试数据：[TB2]

if object_id('[TB2]') is not null drop table [TB2]

go 

create table [TB2]([SKU] int,[Qty] int)

insert [TB2]

select 180050,20

--------------开始查询--------------------------



; with t as

(

	select *,id=row_number() over(order by BatchNo) from[TB1]

)

update a set 

	a.Qty= case when b.[Qty]>(select sum([Qty]) from t where [id]<=a.[id])

		   then 0

	       when b.[Qty]>(select sum([Qty]) from t where [id]<a.[id]) 

				and b.[Qty]<(select sum([Qty]) from t where [id]<=a.[id]+1)

		   then a.[Qty]-(b.[Qty]-(select sum([Qty]) from t where [id]<a.[id]))

		   else a.[Qty]

	      end

from t a,[TB2] b 





select * from tb1  order by BatchNo

/*

SKU         BatchNo  Qty         OrdQty      Volume

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

180050      DY101110 0           1           0.03510

180050      DY111108 0           9           2.50000

180050      DY111109 6           4           1.40832

180050      DY111117 1           1           0.07500



(4 行受影响)



*/
AcHerat 元老 2012-03-13
  • 打赏
  • 举报
 回复
为何我的32楼不见了。
xs0573 2012-03-13
  • 打赏
  • 举报
 回复
如果用over()对qty求sum做子查询，然后对于小于20的插入到tb3 更新tb2成0
对于大等于20的，取最小的更新为sum-20 这样可行吗
没环境做实验，顺口问下
bitls 2012-03-13
  • 打赏
  • 举报
 回复
[Quote=引用 31 楼 maco_wang 的回复:]

引用 27 楼 trunjun 的回复:

能说下有什么BUG吗？

#23 当数字发生变化的时候，偶尔会不准确，#28做了修复，#30做了扩展。
[/Quote]
谢谢老王,结贴.
自己去研究一下,非常感谢!
AcHerat 元老 2012-03-13
  • 打赏
  • 举报
 回复 


create table tb

(SKU int,BatchNo varchar(8),Qty int,OrdQty int,Volume numeric(6,5))

insert into tb

select 180050,'DY101110',1,1,0.0351 union all

select 180050,'DY111117',1,1,0.075 union all

select 180050,'DY111108',18,9,2.5 union all

select 180050,'DY111109',7,4,1.40832

go



create table tb2(sku int,qty int)

insert into tb2

select 180050,20

go



declare @batch varchar(max)

declare @qty int

declare @newqty int



;with ach as

(

	select a.*,b.qty newqty

	from tb a join tb2 b on a.sku = b.sku

	where (select sum(qty) from tb where sku=a.sku and BatchNo<=a.BatchNo) >= b.qty

		and (select sum(qty) from tb where sku=a.sku and BatchNo<a.BatchNo) <= b.qty

)



update a

set @qty=(select sum(qty) from tb where sku=a.sku and BatchNo<=a.BatchNo),

	@newqty = b.newqty - @qty,

	a.qty = (case when @newqty<=0 then @qty-b.newqty

				  when @newqty>0 then 0 end)

from tb a join ach b on a.sku = b.sku and a.BatchNo<=b.BatchNo



select * from tb



drop table tb,tb2



/**************************



SKU         BatchNo  Qty         OrdQty      Volume

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

180050      DY101110 0           1           0.03510

180050      DY111117 1           1           0.07500

180050      DY111108 0           9           2.50000

180050      DY111109 6           4           1.40832



(4 行受影响)
叶子 2012-03-13
  • 打赏
  • 举报
 回复
[Quote=引用 27 楼 trunjun 的回复:]

能说下有什么BUG吗？
[/Quote]
#23 当数字发生变化的时候，偶尔会不准确，#28做了修复，#30做了扩展。
叶子 2012-03-13
  • 打赏
  • 举报
 回复 
--多个编号的更新

--测试数据表1(我把主键id加上了)

go

create table bitlstb1 

(id int primary key,sku int,batchno varchar(8),qty int,ordqty int,volume numeric(6,5))

insert into bitlstb1



select 1,180050,'dy101110',1,1,0.0351 union all

select 2,180050,'dy111117',1,1,0.075 union all

select 3,180050,'dy111108',18,9,2.5 union all

select 4,180050,'dy111109',7,4,1.40832 union all

select 5,180051,'dy101110',3,1,0.0351 union all

select 6,180051,'dy111117',2,1,0.075 union all

select 7,180051,'dy111108',18,9,2.5 union all

select 8,180051,'dy111109',7,4,1.40832



go

--测试数据表2

create table bitlstb2(sku int,qty int)

insert into bitlstb2

select 180050,20 union all

select 180051,10 





go

--创建一个存储过程

create proc updateqty(@p int)

as

begin

	--得到发货数量

	declare @qty int 

	select @qty=qty from bitlstb2 where sku=@p



	declare @j varchar(20);declare @k int 

	--逐行更新



	;with maco as ( select * from bitlstb1 where sku=@p),

	maco1 as(select *,zqty=(select sum(qty) from maco where batchno<=a.batchno) from maco a) 



	select top 1 @j=batchno,@k=zqty-@qty from maco1 where zqty>=@qty order by batchno



	update bitlstb1 set qty=@k where batchno=@j and sku=@p 

	update bitlstb1 set qty=0 where sku=@p and batchno<@j and sku=@p

end





declare @sql varchar(max) set @sql=''

select @sql=@sql+' exec updateqty '+ltrim(sku)+';' from bitlstb2

exec(@sql)



select * from bitlstb1

/*

id          sku         batchno  qty         ordqty      volume

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

1           180050      dy101110 0           1           0.03510

2           180050      dy111117 1           1           0.07500

3           180050      dy111108 0           9           2.50000

4           180050      dy111109 6           4           1.40832

5           180051      dy101110 0           1           0.03510

6           180051      dy111117 2           1           0.07500

7           180051      dy111108 11          9           2.50000

8           180051      dy111109 7           4           1.40832

*/



drop table bitlstb1,bitlstb2
bitls 2012-03-13
  • 打赏
  • 举报
 回复

王哥,你的这上结果是对的,如果是发货表内有多少SKU那,怎么操作

--发货表
/*
SKU Qty
180050 20
180023 10
180034 1
*/
叶子 2012-03-13
  • 打赏
  • 举报
 回复 
--修正一下

--测试数据表1(我把主键id加上了)

declare @tb1 table 

(id int primary key,sku int,batchno varchar(8),qty int,ordqty int,volume numeric(6,5))

insert into @tb1

select 1,180050,'dy101110',1,1,0.0351 union all

select 2,180050,'dy111117',1,1,0.075 union all

select 3,180050,'dy111108',18,9,2.5 union all

select 4,180050,'dy111109',7,4,1.40832



--测试数据表2

declare @tb2 table (sku int,qty int)

insert into @tb2

select 180050,20



--设置参数

declare @p int set @p=180050



--得到发货数量

declare @qty int 

select @qty=qty from @tb2 where sku=180050



declare @j VARCHAR(20);declare @k int 

--逐行更新



;with maco as ( select * from @tb1 where sku=180050),

maco1 as(select *,zqty=(select sum(qty) from maco where batchno<=a.batchno) from maco a) 



select top 1 @j=batchno,@k=zqty-@qty from maco1 where zqty>=@qty order by batchno



update @tb1 set qty=@k where batchno=@j  

update @tb1 set qty=0 where sku=180050 and batchno<@j  

   

--查看结果（按生产批量排序）

select * from @tb1 order by 3



--结果

/*

id          sku         batchno  qty         ordqty      volume

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

1           180050      dy101110 0           1           0.03510

3           180050      dy111108 0           9           2.50000

4           180050      dy111109 6           4           1.40832

2           180050      dy111117 1           1           0.07500

*/
trunjun 2012-03-13
  • 打赏
  • 举报
 回复
能说下有什么BUG吗？
bitls 2012-03-13
  • 打赏
  • 举报
 回复
[Quote=引用 24 楼 maco_wang 的回复:]
有bug ，稍等我处理一下。
[/Quote]

王哥,我上面的发的只是一些测试数据,但以后可能有很多编号一起处理(意思就是发货表内可能有很多少编号),能不能不用游标,用正常SQL语句,越看感觉越菜鸟,看晕了!
trunjun 2012-03-13
  • 打赏
  • 举报
 回复
[Quote=引用 23 楼 maco_wang 的回复:]
SQL code


--测试数据表1
declare @tb1 table
(sku int,batchno varchar(8),qty int,ordqty int,volume numeric(6,5))
insert into @tb1
select 180050,'dy101110',1,1,0.0351 union all
select 180050,'dy1111……
[/Quote]

崇拜中……………………
叶子 2012-03-13
  • 打赏
  • 举报
 回复
有bug ，稍等我处理一下。
叶子 2012-03-13
  • 打赏
  • 举报
 回复 


--测试数据表1

declare @tb1 table 

(sku int,batchno varchar(8),qty int,ordqty int,volume numeric(6,5))

insert into @tb1

select 180050,'dy101110',1,1,0.0351 union all

select 180050,'dy111117',1,1,0.075 union all

select 180050,'dy111108',18,9,2.5 union all

select 180050,'dy111109',7,4,1.40832



--测试数据表2

declare @tb2 table (sku int,qty int)

insert into @tb2

select 180050,20



--设置参数

declare @p int set @p=180050



--可以加上条件例如where sku=180050，然后排序后放到临时表中

select * into #tb3 from @tb1 where sku=180050 order by batchno



--得到发货数量

declare @qty int 

select @qty=qty from @tb2 where sku=180050



--逐行更新

update  #tb3

set     @qty = case when @qty < 0 then 0 else @qty - qty end ,

        qty = case when @qty > 0 then 0 when @qty = 0 then qty else -@qty end

        

--把临时表的结果再更新到原表中，正常是主键关联（关联改成你的id主键即可）

update  @tb1 set qty = b.qty from @tb1 a

left join #tb3 b on a.batchno = b.batchno



--查看结果（按生产批量排序）

select * from @tb1 order by 2



--结果

/*

sku         batchno  qty         ordqty      volume

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

180050      dy101110 0           1           0.03510

180050      dy111108 0           9           2.50000

180050      dy111109 6           4           1.40832

180050      dy111117 1           1           0.07500

*/



drop table #tb3
trunjun 2012-03-13
  • 打赏
  • 举报
 回复
NB人，强烈顶起，哈哈
bitls 2012-03-13
  • 打赏
  • 举报
 回复
[Quote=引用 18 楼 maco_wang 的回复:]
表的主键是什么？

排序后放到临时表中，然后临时表中逐行减少，减到数量为止。

然后按主键更新回来即可。
[/Quote]
表只有一个ID为主键,其它的没有,还请王哥帮忙!
bitls 2012-03-13
  • 打赏
  • 举报
 回复
[Quote=引用 18 楼 maco_wang 的回复:]
表的主键是什么？

排序后放到临时表中，然后临时表中逐行减少，减到数量为止。

然后按主键更新回来即可。
[/Quote]
库存表内只有一个ID为主键,其它的没有.
bitls 2012-03-13
  • 打赏
  • 举报
 回复 
SELECT * INTO #t FROM @TB1 ORDER BY BatchNo

DECLARE @i INT SET @i=20

update #t

set @i=case when @i<0 then 0 else @i-Qty end,

Qty=case when @i>0 then 0 when @i=0 then Qty else -@i END



UPDATE @TB1 SET Qty=b.Qty

FROM @TB1 a LEFT JOIN #t b ON a.BatchNo=b.BatchNo

select * from @TB1


王哥, @TB1及@i什么意思,能不能帮忙转成正常的SQL语句,不用游标,我是个菜鸟.
另外,王哥,能不能把操作库存表那些行,给插入到TB3表内
非常感谢,解决了,马上结贴!
加载更多回复（18）
java中update怎么写,update语句
手机评站网今天精心准备的是《update语句》，下面是详解！SQL的update语句怎么写？当a列的值为f时,对x表的b，c，d，e列同时更新数据...当a列的值为f时,对x表的b，c，d，e列同时更新数据UPDATE 表名称 SET 列名称 = ...
UPDATE语句如何关联两个表
UPDATE语句如何关联两个表
Insert into select语句和Update语句
语法介绍 (1)如果我们要将其它表中的数据插入另一个表中，我们该如何插入呢？ Insert into select：将其它表数据...Update：使用update语句可以修改表中现有的数据 语法如下： 1.更新某个值 Update 模式.表名 set
oracle动态update语句
2、OK，debugger一下，发现提交的时候后台打印出错，提示大概就是这样 SQLException '17004' 其实很简单，这种错误一般都是说某一列传进来的值为null，因此报错，所以追踪下去，发现是update语句没有做动态传值处理...
oracle中sql建立索引调优,关于update的SQL调优，怎样才能让update语句走索引？？
关于update的SQL调优，怎样才能让update语句走索引？？批量更新一个表，根据执行计划得到的是：全表扫描。尽管已经建立索引列。测试过程SQL> desc dfj_test2Name Null? Type------------------------------------...
MS-SQL Server

34,593

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章