MySql语句中，select和update使用case when then end笔记

在日常项目中，mysql的case when then end还是比较有意思的，请看例子：

select的使用

数据表结构：

执行语句：

SELECT
count(*),
CASE
    WHEN role_count between 1 and 100 THEN '一般'
    WHEN role_count between 101 and 1000 THEN '不错'
    WHEN role_count between 1001 and 5000 THEN '很棒'
    WHEN role_count >= 5001 THEN '非常棒'
ELSE '弱爆了'
END 
AS pj
FROM statis_server_rolecount_recharge GROUP BY pj;

 按照role_count字段的值做统计。

---

 update的使用

数据表结构：

执行语句：

update `statis_server_rolecount_recharge` set role_count = 
case 
    when gid = 1 and server_id = 104020012 then role_count + 100
    when gid = 1 and server_id = 104020000 then role_count + 100 
    when gid = 1 and server_id = 104020007 then role_count + 100 
    when gid = 1 and server_id = 104020010 then role_count + 100 
    when gid = 1 and server_id = 104020011 then role_count + 100 
    when gid = 1 and server_id = 104020006 then role_count + 100 
    when gid = 1 and server_id = 104020009 then role_count + 100 
    when gid = 1 and server_id = 104020003 then role_count + 100 
    when gid = 1 and server_id = 104020008 then role_count + 100 
    when gid = 1 and server_id = 104020001 then role_count + 100 
end;

执行代码结果：

可以看到，在原来role_count的字段上，每个都加了100

注意：我这里为了做测试，没有加任何条件，实际项目中，务必要控制好更新的条件哦！