if、case...when...then

if语法：IF(expr1,expr2,expr3)
其中，expr1是判断条件，expr2和expr3分别是符合expr1和不符合expr1的返回结果。
如我们想把销售量大于100的产品记为热销，其他记为不热销，

SELECT b.brand,
IF(b.sale > 100 , '热销', '不热销') AS hot_or_not
FROM db_data_jiadian.t_brand_sale AS b

结果：

这样只能处理两种情况，要处理多种情况用case...when。

SELECT b.brand AS '品牌', 

CASE
WHEN b.sale = 0 THEN '未售出'
WHEN b.sale < 100 THEN '非爆款'
ELSE '爆款'
END AS '销售情况'

FROM db_data_jiadian.t_brand_sale AS b

结果：

注意匹配时匹配到第一个符合的项就结束匹配，所以sale==0时会匹配未售出，而不会再匹配非爆款。

再来一个曾经面试时被问到过的行转列/列转行的问题。比如表结构是item_id, param_name, param_value，最后想变成每个item_id一行，取某些param_name值作为列名，则：

SELECT item_id,max(nengxiao),max(neijizaoyin),max(waijizaoyin),max(pishu),max(bianpin) from(
SELECT item_id, 
 CASE WHEN param_name ="能效等级"  THEN param_value  END AS 'nengxiao',
 CASE WHEN param_name="内机噪音"  THEN param_value  END AS  'neijizaoyin' ,
  CASE WHEN param_name ="外机噪音"  THEN param_value  END AS  "waijizaoyin" ,
  CASE WHEN param_name = "匹数"      THEN  param_value  END AS "pishu" ,
  CASE WHEN param_name = "定频/变频" THEN   param_value  END AS "bianpin" 

 FROM db_midea_data_jiadian.temp_item_param) a 
GROUP BY item_id