if語(yǔ)法：IF(expr1,expr2,expr3)
其中带迟，expr1是判斷條件音羞，expr2和expr3分別是符合expr1和不符合expr1的返回結(jié)果。
如我們想把銷(xiāo)售量大于100的產(chǎn)品記為熱銷(xiāo)仓犬，其他記為不熱銷(xiāo)嗅绰，
SELECT b.brand,
IF(b.sale > 100 , '熱銷(xiāo)', '不熱銷(xiāo)') AS hot_or_not
FROM db_data_jiadian.t_brand_sale AS b
結(jié)果：
這樣只能處理兩種情況，要處理多種情況用case...when搀继。
SELECT b.brand AS '品牌',
CASE
WHEN b.sale = 0 THEN '未售出'
WHEN b.sale < 100 THEN '非爆款'
ELSE '爆款'
END AS '銷(xiāo)售情況'
FROM db_data_jiadian.t_brand_sale AS b
結(jié)果：
注意匹配時(shí)匹配到第一個(gè)符合的項(xiàng)就結(jié)束匹配窘面，所以sale==0時(shí)會(huì)匹配未售出，而不會(huì)再匹配非爆款叽躯。
再來(lái)一個(gè)曾經(jīng)面試時(shí)被問(wèn)到過(guò)的行轉(zhuǎn)列/列轉(zhuǎn)行的問(wèn)題财边。比如表結(jié)構(gòu)是item_id, param_name, param_value，最后想變成每個(gè)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 ="能效等級(jí)" THEN param_value END AS 'nengxiao',
CASE WHEN param_name="內(nèi)機(jī)噪音" THEN param_value END AS 'neijizaoyin' ,
CASE WHEN param_name ="外機(jī)噪音" THEN param_value END AS "waijizaoyin" ,
CASE WHEN param_name = "匹數(shù)" 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
