一个表 产品 数量 dvd 10 dvd 30 dvd 50 cd 40 cd 20 你要的结果是什么样的~
结果 dvd 0 dvd 10 dvd 50 cd 40 cd 20
结果 dvd 0 dvd 20 dvd 50 cd 40 cd 20 也就是上面不够再扣下面数
应该是 dvd 0 dvd 20 dvd 50 cd 40 cd 20 还是dvd 0 dvd 10 dvd 50 cd 40 cd 20 ?前者意味着,总共扣20 后者意味着, 每行扣20,当某行满足要求时停止扣除。
create table #tb(prod varchar(50),qty int) insert #tb select 'dvd',10 union all select 'dvd',30 union all select 'dvd',50 union all select 'cd',40 union all select 'cd',20 declare @prod varchar(50),@qty int select @prod='dvd',@qty=20declare @decqty intupdate #tb set @decqty=case when @qty>qty then qty else @qty end ,qty=qty-@decqty,@qty=@qty-@decqty where prod=@prod and @qty>0select * from #tbdrop table #tb
得到这种结果,不知是不是楼主要的效果: dvd 0 dvd 20 dvd 50 cd 40 cd 20
结果:当某行满足要求时停止扣除。 还有不知道DVD里有多少个数量值.
山羊:)解中知道表里DVD有多个少值, 但我现在当中我不知道里面有多个个值. 谢谢 山羊:)
举例中DVD 数量有三个, 实际上不知道表中DVD有多少个数量值.
DVD数量有90啊,记录才是三条
DECLARE @t TABLE(id INT IDENTITY(1,1),n VARCHAR(10),m INT) INSERT @t select 'dvd',10 union all select 'dvd',30 union all select 'dvd',50 union all select 'cd',40 union all select 'cd',20 DECLARE @n VARCHAR(10),@m INT SELECT @n='dvd',@m=20 --将这里换成你的值。 --表中若无标识列,则先生成临时表产生identity列。 UPDATE a SET m=CASE WHEN m>=@m THEN m-@m ELSE 0 END FROM @t a WHERE id<=(SELECT TOP 1 id FROM @t WHERE m>=@m AND n=@n) AND n=@nSELECT * FROM @t
select sum(qty) as totalqty from #tb where prod='dvd'这不就知道了DVD的数量吗?
create table #tb(prod varchar(50),qty int) insert #tb select 'dvd',10 union all select 'dvd',30 union all select 'dvd',50 union all select 'cd',40 union all select 'cd',20 declare @prod varchar(50),@qty int select @prod='dvd',@qty=20declare @decqty int,@decrows int,@totalrows intselect @decrows=0,@totalrows=0 update #tb set @decqty=case when @qty>qty then qty else @qty end ,qty=qty-@decqty,@qty=@qty-@decqty ,@decrows=case when @decqty>0 then @decrows+1 else @decrows end ,@totalrows= @totalrows+1 where prod=@prod--select * from #tbselect 扣除记录条数=@decrows,总记录条数=@totalrowsdrop table #tb
declare @t table(产品 varchar(10),数量 int) insert @t select 'dvd', 10 union select 'dvd', 30 union select 'dvd', 50 union select 'cd', 40 union select 'cd', 20 declare @i int set @I = 20update @t set 数量 = case when @I > 0 then 0 when @I = -1 then 数量 else -@i end ,@i = case when @I > 0 then @I - 数量 else -1 end where 产品 = 'dvd' select * from @t/* 产品 数量 ---------- ----------- cd 20 cd 40 dvd 0 dvd 20 dvd 50 */
declare @t table(产品 varchar(10),数量 int) insert @t select 'dvd', 10 union select 'dvd', 30 union select 'dvd', 50 union select 'cd', 40 union select 'cd', 20 declare @i int set @I = 20update @t set 数量 = case when @I > 0 then 0 when @I = -1 then 数量 else -@i end ,@i = case when @I > 0 then @I - 数量 else -1 end where 产品 = 'dvd' select * from @t/* 产品 数量 ---------- ----------- cd 20 cd 40 dvd 0 dvd 20 dvd 50 */
谢谢大家的帮助 现在又遇到个小问题: 想得到所有被修改过做个标记 结果如: dvd 0 1 dvd 20 0 dvd 50 0 cd 40 0 cd 20 0 各位高手帮忙再看
不好意思 结果写错了 dvd 0 1 dvd 20 1 dvd 50 0 cd 40 0 cd 20 0 因被修改两个 create table #tb(prod varchar(50),qty int,flag int ) insert #tb select 'dvd',10 union all select 'dvd',30 ,0 union all select 'dvd',50 ,0 union all select 'cd',40 ,0 union all select 'cd',20 ,0declare @prod varchar(50),@qty int select @prod='dvd',@qty=20declare @decqty int,@decrows int,@totalrows intselect @decrows=0,@totalrows=0 update #tb set @decqty=case when @qty>qty then qty else @qty end ,qty=qty-@decqty,@qty=@qty-@decqty ,flag=1, ,@decrows=case when @decqty>0 then @decrows+1 else @decrows end ,@totalrows= @totalrows+1 where prod=@prod这样返回值是dvd 0 1 dvd 20 1 dvd 50 1 cd 40 1 cd 20 1想得到有被修改才做标记. 结果如dvd 0 1 dvd 20 1 dvd 50 0 cd 40 0 cd 20 0
DECLARE @t TABLE(id INT IDENTITY(1,1),n VARCHAR(10),m INT) INSERT @t select 'dvd',10 union all select 'dvd',30 union all select 'dvd',50 union all select 'cd',40 union all select 'cd',20 DECLARE @n VARCHAR(10),@m INT,@m1 INT SELECT @n='dvd',@m=20,@m1=0 --将这里换成你的值。update @t set @m1=case when @m>=m then m else @m end , @m=@m-@m1, m=m-@m1 from @t where n=@n and @m>0select * from @t
dvd 10
dvd 30
dvd 50
cd 40
cd 20 你要的结果是什么样的~
dvd 10
dvd 50
cd 40
cd 20
dvd 20
dvd 50
cd 40
cd 20
也就是上面不够再扣下面数
dvd 20
dvd 50
cd 40
cd 20 还是dvd 0
dvd 10
dvd 50
cd 40
cd 20 ?前者意味着,总共扣20
后者意味着, 每行扣20,当某行满足要求时停止扣除。
insert #tb
select 'dvd',10
union all select 'dvd',30
union all select 'dvd',50
union all select 'cd',40
union all select 'cd',20 declare @prod varchar(50),@qty int
select @prod='dvd',@qty=20declare @decqty intupdate #tb
set @decqty=case when @qty>qty then qty else @qty end
,qty=qty-@decqty,@qty=@qty-@decqty
where prod=@prod and @qty>0select * from #tbdrop table #tb
dvd 20
dvd 50
cd 40
cd 20
还有不知道DVD里有多少个数量值.
但我现在当中我不知道里面有多个个值.
谢谢 山羊:)
实际上不知道表中DVD有多少个数量值.
DVD数量有90啊,记录才是三条
INSERT @t select 'dvd',10
union all select 'dvd',30
union all select 'dvd',50
union all select 'cd',40
union all select 'cd',20 DECLARE @n VARCHAR(10),@m INT
SELECT @n='dvd',@m=20 --将这里换成你的值。
--表中若无标识列,则先生成临时表产生identity列。
UPDATE a SET m=CASE WHEN m>=@m THEN m-@m ELSE 0 END
FROM @t a
WHERE id<=(SELECT TOP 1 id FROM @t WHERE m>=@m AND n=@n)
AND n=@nSELECT * FROM @t
实际当中DVD记录可能有一条,也有可能二条,三条,四条这个是不确定值.
insert #tb
select 'dvd',10
union all select 'dvd',30
union all select 'dvd',50
union all select 'cd',40
union all select 'cd',20 declare @prod varchar(50),@qty int
select @prod='dvd',@qty=20declare @decqty int,@decrows int,@totalrows intselect @decrows=0,@totalrows=0
update #tb
set @decqty=case when @qty>qty then qty else @qty end
,qty=qty-@decqty,@qty=@qty-@decqty
,@decrows=case when @decqty>0 then @decrows+1 else @decrows end
,@totalrows= @totalrows+1
where prod=@prod--select * from #tbselect 扣除记录条数=@decrows,总记录条数=@totalrowsdrop table #tb
insert @t select
'dvd', 10 union select
'dvd', 30 union select
'dvd', 50 union select
'cd', 40 union select
'cd', 20
declare @i int
set @I = 20update @t
set 数量 = case when @I > 0 then 0 when @I = -1 then 数量 else -@i end
,@i = case when @I > 0 then @I - 数量 else -1 end
where 产品 = 'dvd'
select * from @t/*
产品 数量
---------- -----------
cd 20
cd 40
dvd 0
dvd 20
dvd 50
*/
insert @t select
'dvd', 10 union select
'dvd', 30 union select
'dvd', 50 union select
'cd', 40 union select
'cd', 20
declare @i int
set @I = 20update @t
set 数量 = case when @I > 0 then 0 when @I = -1 then 数量 else -@i end
,@i = case when @I > 0 then @I - 数量 else -1 end
where 产品 = 'dvd'
select * from @t/*
产品 数量
---------- -----------
cd 20
cd 40
dvd 0
dvd 20
dvd 50
*/
现在又遇到个小问题:
想得到所有被修改过做个标记
结果如:
dvd 0 1
dvd 20 0
dvd 50 0
cd 40 0
cd 20 0
各位高手帮忙再看
结果写错了
dvd 0 1
dvd 20 1
dvd 50 0
cd 40 0
cd 20 0
因被修改两个
create table #tb(prod varchar(50),qty int,flag int )
insert #tb
select 'dvd',10
union all select 'dvd',30 ,0
union all select 'dvd',50 ,0
union all select 'cd',40 ,0
union all select 'cd',20 ,0declare @prod varchar(50),@qty int
select @prod='dvd',@qty=20declare @decqty int,@decrows int,@totalrows intselect @decrows=0,@totalrows=0
update #tb
set @decqty=case when @qty>qty then qty else @qty end
,qty=qty-@decqty,@qty=@qty-@decqty ,flag=1,
,@decrows=case when @decqty>0 then @decrows+1 else @decrows end
,@totalrows= @totalrows+1
where prod=@prod这样返回值是dvd 0 1
dvd 20 1
dvd 50 1
cd 40 1
cd 20 1想得到有被修改才做标记.
结果如dvd 0 1
dvd 20 1
dvd 50 0
cd 40 0
cd 20 0
INSERT @t select 'dvd',10
union all select 'dvd',30
union all select 'dvd',50
union all select 'cd',40
union all select 'cd',20 DECLARE @n VARCHAR(10),@m INT,@m1 INT
SELECT @n='dvd',@m=20,@m1=0 --将这里换成你的值。update @t set @m1=case when @m>=m then m else @m end ,
@m=@m-@m1,
m=m-@m1
from @t where n=@n and @m>0select * from @t