1.语法
语法示例一:
CASE SCORE WHEN 'A' THEN '优' ELSE '不及格' END
CASE SCORE WHEN 'B' THEN '良' ELSE '不及格' END
CASE SCORE WHEN 'C' THEN '中' ELSE '不及格' END
语法示例二:
CASE WHEN SCORE = 'A' THEN '优'
WHEN SCORE = 'B' THEN '良'
WHEN SCORE = 'C' THEN '中' ELSE '不及格' END
语法示例三:
CASE WHEN (SCORE = 'A' and 1=1 and 1=1) THEN '优'
WHEN (SCORE = 'B' and 1=1 and 1=1) THEN '良'
WHEN (SCORE = 'C' and 1=1 and 1=1) THEN '中' ELSE '不及格' END
语法示例四:
CASE WHEN score IS NULL THEN '缺席考试' ELSE '正常' END
注意事项:
1.THEN后边的值与ELSE后边的值类型应一致,否则会报错。
2.WHEN后边一定是个boolean
2.复杂使用示例
SELECT
it.`transaction_batch` AS `code`,
CASE WHEN (it.`source_type` ='TALLY_ORDER' and it.transaction_type in (1,2) and (
SELECT
count(*)
FROM
mengniu_mnur.`t_tally_track` AS tat
LEFT JOIN mengniu_mnur.`t_replenish_order_tally_detail` AS trotd ON tat.`event_id` = trotd.`tally_track_id`
LEFT JOIN mengniu_mnur.`t_replenish_order_tally` AS trot ON trotd.`car_order_id` = trot.`car_order_id`
WHERE
tat.`tally_order_id` = it.`source_id`) > 0) THEN (IF(it.transaction_type=1,'a','b'))
WHEN (it.`source_type`='TALLY_STOCK_CORRECTION' and it.transaction_type = 8 and (
SELECT
count(*)
FROM
mengniu_mnur.`t_tally_stock_correction` tasc
LEFT JOIN mengniu_mnur.`t_tally_track` tat ON tasc.`tally_order_id` = tat.`tally_order_id`
LEFT JOIN mengniu_mnur.`t_replenish_order_tally_detail` trotd ON tat.`event_id` = trotd.`tally_track_id`
LEFT JOIN mengniu_mnur.`t_replenish_order_tally` trot ON trotd.`car_order_id` = trot.`car_order_id`
WHERE
tasc.`correction_id` = it.`source_id`
)>0) THEN 'c'
ELSE v_type.`meaning` END AS `type`
FROM t_asdfghjkl it