SQL CASE WHEN 复杂使用

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