表 Test
有字段 ID UnitQty TotalQty
1 2 100
2 1 150
3 5 600
...
此时有一常数 Qty = 60 , 需计算 TotalQty = TotalQty - UnitQty * Qty
且 如果TotalQty < 0 则 TotalQty = 0
使得结果如下:
ID UnitQty TotalQty
1 2 0
2 1 90
3 5 300
...
先谢了.
---------------------------------------------------------------
不好意思，反了
declare @qty int
set @qty=60
update test set tptalqty= TotalQty - UnitQty * Qty
2）update test set tptalqty=0 where tptalqty<0
---------------------------------------------------------------
declare @qty int
set @qty=60
update test set tptalqty=(case when TotalQty - UnitQty * Qty>0
then TotalQty - UnitQty * Qty else 0 end)
---------------------------------------------------------------
update test set tptalqty=
CASE (TotalQty - UnitQty * Qty)
when (TotalQty - UnitQty * Qty)<0 then 0
else TotalQty - UnitQty * Qty
END
这个看看呢！
---------------------------------------------------------------
update test set tptalqty=(case when TotalQty - UnitQty * 60>0
then TotalQty - UnitQty * 60 else 0 end)
---------------------------------------------------------------
update test set tptalqty=
（
CASE
when (TotalQty - UnitQty * Qty)<0 then 0
else TotalQty - UnitQty * Qty
END ）
---------------------------------------------------------------
update test set tptalqty =
case
when TotalQty - UnitQty * Qty < 0 then 0
else TotalQty - UnitQty * Qty
end,