mysql case多条件比较

mysql case多条件比较

##正确写法 then (100-tt.fineAmt)  也可以写表达式
SELECT order_no,
CASE 
WHEN (days >= 1 AND days < 10) THEN '0'
WHEN (days >= 10 AND days < 20) THEN '1'
WHEN (days >= 20 AND days < 30) THEN '4'
WHEN (days >= 30)  THEN '5'
ELSE '0'
END AS 'type'
 FROM `rent_discount`  t
WHERE NOT EXISTS (SELECT 1 FROM rent_discount WHERE order_no = t.`order_no` AND id > t.id)
AND t.order_no = '111';

##错误写法，多了case when 语法格式错误。
SELECT order_no,
 CASE WHEN (days >= 1 AND days < 10) THEN '0'
CASE WHEN (days >= 10 AND days < 20) THEN '1'
CASE WHEN (days >= 20 AND days < 30) THEN '4'
CASE WHEN (days >= 30)  THEN '5'
END AS 'type'
 FROM `rent_discount`  t
WHERE NOT EXISTS (SELECT 1 FROM rent_discount WHERE order_no = t.`order_no` AND id > t.id)
AND t.order_no = '111';