mysql中 case 的使用

1.聚合函数sum和case一起使用 

SELECT sum(case 
                when typeid=88 then money/0.7 
                when typeid=20 then money/0.5 
else  money end) as money FROM `trades` where creatime>'2018-01-01';

2.update和case一起使用 

update game_niudanjiactivityrewards set type= ( CASE 
                                                    WHEN right(rewards, 2)='积分' THEN 3 
                                                    WHEN right(rewards, 2)='金币' THEN 2 
                                                    ELSE 1 END
                                              )

 right(rewards, 2)='积分' ------- 意思是字段 rewards 最后两个字是  积分

3.case给每一项不同的值取别名

"SELECT DATE_FORMAT(Creatime,'%Y-%m-%d') AS creatime ,
                MAX(CASE id WHEN 1 THEN score ELSE 0 END) AS a,
                MAX(CASE id WHEN 2 THEN score ELSE 0 END) AS b,
                MAX(CASE id WHEN 3 THEN score ELSE 0 END) AS c,
                SUM(score)
                FROM tableName   
                WHERE 1=1 and Creatime >='2018-01-01'";