这是我的表格。
我需要做的是创建一个CASE WHEN UPDATE查询来更新:
如果在1-15范围内,将CurrentCredit加1
如果在16-30范围内,将CurrentCredit加2
如果大于等于31,将CurrentCredit乘以0.10并四舍五入
请查看下面的示例
类似于以下内容
UPDATE MyTable
SET CurrentCredit =
CASE
WHEN CurrentCredit BETWEEN 1 AND 15
THEN CurrentCredit + 1
WHEN CurrentCredit BETWEEN 16 AND 30
THEN CurrentCredit +2
WHEN CurrentCredit >= 31
THEN CurrentCredit * 10
ELSE CurrentCredit
END
另外,请记住,如果CurrentCredit是一个FLOAT而不是INT,您可能需要尝试类似以下的操作:
UPDATE MyTable
SET CurrentCredit =
CASE
WHEN CurrentCredit >= 1 AND CurrentCredit < 16
THEN CurrentCredit + 1
WHEN CurrentCredit >= 16 AND CurrentCredit < 31
THEN CurrentCredit +2
WHEN CurrentCredit >= 31
THEN CurrentCredit * 10
ELSE CurrentCredit
END;
编辑
如果需要更正的请求是(>= 31 CurrentCredit + CEILING(CurrentCredit * 0.1)),请考虑使用CEILING函数。
UPDATE MyTable
SET CurrentCredit =
CASE
WHEN CurrentCredit BETWEEN 1 AND 15
THEN CurrentCredit + 1
WHEN CurrentCredit BETWEEN 16 AND 30
THEN CurrentCredit +2
WHEN CurrentCredit >= 31
THEN CurrentCredit + CEILING(CurrentCredit * 0.1)
ELSE CurrentCredit
END;