update titles set price=case when price>15 and price<=20 then price*2 when price>20 then price*4 end where price<30
--写了一个游标,看看可以吗?declare cur_price cursor dynamic for select price from titles declare @price money open cur_price fetch next from cur_price into @price while (@@fetch_status = 0) begin if (@price <30) begin if (@price >20) update titles set price=price*4 WHERE CURRENT OF cur_price else if(@price >15) update titles set price=price*2 WHERE CURRENT OF cur_price end fetch next from cur_price into @price end CLOSE cur_price DEALLOCATE cur_price
--测试 --创建测试表 create table titles(id int,price money) --插入数据 insert titles select 1,21 --应该变成84 union select 2,42 union select 3,22 --应该变成88 union select 4,16 --应该变成32 union select 5,5--执行游标declare cur_price cursor dynamic for select price from titles declare @price money open cur_price fetch next from cur_price into @price while (@@fetch_status = 0) begin if (@price <30) begin if (@price >20) update titles set price=price*4 WHERE CURRENT OF cur_price else if(@price >15) update titles set price=price*2 WHERE CURRENT OF cur_price end fetch next from cur_price into @price end CLOSE cur_price DEALLOCATE cur_priceselect * from titles --结果 /* id price ----------- --------------------- 1 84.0000 2 42.0000 3 88.0000 4 32.0000 5 5.0000(所影响的行数为 5 行) */ --测试成功!
when price>20 then price*4
end
where price<30
for select price from titles
declare @price money
open cur_price
fetch next from cur_price into @price
while (@@fetch_status = 0)
begin
if (@price <30)
begin
if (@price >20)
update titles set price=price*4 WHERE CURRENT OF cur_price
else if(@price >15)
update titles set price=price*2 WHERE CURRENT OF cur_price
end
fetch next from cur_price into @price
end
CLOSE cur_price
DEALLOCATE cur_price
--创建测试表
create table titles(id int,price money)
--插入数据
insert titles
select 1,21 --应该变成84
union select 2,42
union select 3,22 --应该变成88
union select 4,16 --应该变成32
union select 5,5--执行游标declare cur_price cursor dynamic
for select price from titles
declare @price money
open cur_price
fetch next from cur_price into @price
while (@@fetch_status = 0)
begin
if (@price <30)
begin
if (@price >20)
update titles set price=price*4 WHERE CURRENT OF cur_price
else if(@price >15)
update titles set price=price*2 WHERE CURRENT OF cur_price
end
fetch next from cur_price into @price
end
CLOSE cur_price
DEALLOCATE cur_priceselect * from titles
--结果
/*
id price
----------- ---------------------
1 84.0000
2 42.0000
3 88.0000
4 32.0000
5 5.0000(所影响的行数为 5 行)
*/
--测试成功!