带有数值的 Mysql case 语句

Question

我有 table

+------------+-------+-------+
| tran_value | date  | name  |
+------------+-------+-------+
|         10 | 22may | mark  |
|         15 | 23may | mark  |
|         16 | 24may | mark  |
|        300 | 19jun | john  |
|        400 | 20jun | john  |
|        500 | 21jun | john  |
|        600 | 22jun | john  |
|        150 | 12dec | chris |
|        160 | 13dec | chris |
|        170 | 14dec | chris |
|        180 | 15dec | chris |
+------------+-------+-------+

我尝试在新列中添加异常值，但它抛出错误

select date,tran_value,
case name
when name ="mark" and tran_value <=15 then new_trn =NULL
  else tran_value
when name ="john" and tran_value <400 then new_trn =NULL
  else tran_value
when name ="chris" and tran_value <170 then new_trn =NULL
  else tran_value

ii.e

+------------+-------+-------+
| tran_value | date  | name  |
+------------+-------+-------+
| NULL       | 22may | mark  |
| NULL       | 23may | mark  |
| 16         | 24may | mark  |
| NULL       | 19jun | john  |
| NULL       | 20jun | john  |
| 500        | 21jun | john  |
| 600        | 22jun | john  |
| NULL       | 12dec | chris |
| NULL       | 13dec | chris |
| 170        | 14dec | chris |
| 180        | 15dec | chris |
+------------+-------+-------+

代码中的任何修改......提前致谢

Answer 1

SELECT date,tran_value,CASE 
        WHEN name ="mark" and tran_value <=15 THEN new_trn =NULL         
        WHEN name ="john" and tran_value <400 THEN new_trn =NULL         
        WHEN name ="chris" and tran_value <170 THEN new_trn =NULL
        ELSE tran_value
END AS new_column;