求UPDATE语句.在线等,谢谢

Question

本帖最后由 bitls 于 2012-03-13 15:34:23 编辑

Answer 1

好像与我的匀单问题｛ http://topic.csdn.net/u/20111101/17/161ea9d0-31de-4cd8-9b8d-63928c6e9a6a.html ｝差不多
应该简单一些，是单组匀单

Answer 2

TravyLee表示不一行一行的更新没啥好的办法了
UPDATE TB1 SET QTY=0 WHERE BATCKNO=DY101110
UPDATE TB1 SET QTY=0 WHERE BATCKNO='DY101110' or BATCKNO='DY101108'
UPDATE TB1 SET QTY=6 WHERE BATCKNO='DY101117'
坐等小F

Answer 3

求一更新语句,BatchNo字段升序排序后,UPDATE->QTY字段
--半把操作的库存表的字段插入后TB3
这个不怎么理解，是更新呢，还是插入呢。

Answer 4

直接插入就行了,顺序没有关系,库存表更新的时候注意BatchNo的顺序就行了..

Answer 5

UPDATE TB1 SET QTY=0 WHERE BATCKNO=‘DY101110’
UPDATE TB1 SET QTY=0 WHERE BATCKNO='DY101110' or BATCKNO='DY101108'
UPDATE TB1 SET QTY=6 WHERE BATCKNO='DY101117'--更新insert #t
select * from table1--插入

Answer 6

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.40832select * from @TB1
--发货发了20个，分别是DY101110取1个，DY111117取1个，DY111108取12个，DY111109取6个？
--为什么这么取呢？规则是什么？
/*
SKU         BatchNo  Qty         OrdQty      Volume
----------- -------- ----------- ----------- ---------------------------------------
180050      DY101110 1           1           0.03510
180050      DY111117 1           1           0.07500
180050      DY111108 18          9           2.50000
180050      DY111109 7           4           1.40832
*/

Answer 7

--库存表
/* 
TB1
编号    生产批号     数量    包装数量    体积
SKU    BatchNo     Qty    OrdQty    Volume
SKU BatchNo Qty OrdQty Volume
180050 DY101110 1 1 0.0351
180050 DY111108 18 9 2.5
180050 DY111109 7 4 1.40832
180050 DY111117 1 1 0.075
*/
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
各位大牛,不明白,我在此楼解释一下;
公司的发货原则是先生产的先发货(FIFO),所以要根据生产批号进行排序,然后发货表的数据,去更新库存表,他们有相同的编号. 我把上面测试数据的顺序排了一下.这样应该好看了吧

Answer 8

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.40832SELECT * 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 ENDUPDATE @TB1 SET Qty=b.Qty
FROM @TB1 a LEFT JOIN #t b ON a.BatchNo=b.BatchNo
select * from @TB1
/*
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
*/
DROP TABLE #t

Answer 9

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 ENDUPDATE @TB1 SET Qty=b.Qty
FROM @TB1 a LEFT JOIN #t b ON a.BatchNo=b.BatchNo
select * from @TB1
王哥, @TB1及@i什么意思,能不能帮忙转成正常的SQL语句,不用游标,我是个菜鸟.
另外,王哥,能不能把操作库存表那些行,给插入到TB3表内
非常感谢,解决了,马上结贴!

Answer 10

--测试数据表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

Answer 11

有bug ，稍等我处理一下。

Answer 12

王哥,我上面的发的只是一些测试数据,但以后可能有很多编号一起处理(意思就是发货表内可能有很多少编号),能不能不用游标,用正常SQL语句,越看感觉越菜鸟,看晕了!

Answer 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=180050declare @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 batchnoupdate @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
*/

Answer 14

王哥,你的这上结果是对的,如果是发货表内有多少SKU那,怎么操作--发货表
/*
SKU    Qty
180050    20
180023    10
180034    1
*/ 

Answer 15

--多个编号的更新
--测试数据表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 bitlstb1select 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.40832go
--测试数据表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

Answer 16

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
gocreate table tb2(sku int,qty int)
insert into tb2
select 180050,20
godeclare @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.BatchNoselect * from tbdrop 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 行受影响)

Answer 17

如果用over()对qty求sum做子查询，然后对于小于20的插入到tb3 更新tb2成0
对于大等于20的，取最小的更新为sum-20 这样可行吗
没环境做实验，顺口问下

Answer 18

擦.............刚忙了一会
等写出来，就结贴了看来偶果然不是大牛
--> 测试数据：[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 行受影响)*/

Answer 19

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.skuselect * from @TB1