MYSQL CASE WHEN UPDATE 查询

Question

这是我的 table 。

我需要做的是创建一个 CASE WHEN UPDATE 查询来更新

• CurrentCredit +1 if 1-15

• 如果 16-30 岁，CurrentCredit +2

• CurrentCredit x.10，如果 >=31 则四舍五入

Answer 1

看例子

SQL Fiddle DEMO

有点像

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;

SQL Fiddle DEMO