"""
使用情况：等值转换，范围转换，列转行
操作方式：两种
1:将case后的字段值与when后面的值进行比较，简单分类
select 字段1, 字段2,
case 字段3
when 值1 then 新值
when 值2 then 新值
end as 重新命名字段3的名字
from table
where ……
order by ……
2：case后无值，根据when逻辑表达式判定
select 字段1, 字段2,
case
when 字段 > 值1 then 新值
when 字段 < 值1 then 新值
else 新值
end as 重新命名字段3的名字
from table
where ……
order by ……
"""
原始数据展示
select * from one_buyer
范围转换
select id,name,case when the_times >=70 then ‘young‘ else ‘old‘ end as age from one_buyer;
等值转换
select id,name,case ser_id_id when 1 then ‘fenda‘ when 2 then ‘baisuishan‘ else ‘kele‘ end as goods from one_buyer
范围转换 --- 两个语句同样的效果
update one_buyer set gender = case gender > 70 when true then 1 else 0 end; update one_buyer set gender = case when gender > 70 then 1 else 0 end;
行转列
"""
5,6,7为列转行，进阶语句，最终执行7将生成列转行视图，6则将查询结果返回，6为5的分组进阶
参考链接：https://blog.csdn.net/weixin_44770933/article/details/106992881?utm_medium=distribute.pc_relevant.none-task-blog-baidujs_title-0&spm=1001.2101.3001.4242
"""
原始数据
sql5 = "select name, case when grade = ‘primary‘ then rate else 0.00 end as ‘primary‘ , case when grade = ‘middle‘ then rate else 0.00 end as ‘middle‘ , case when grade = ‘high‘ then rate else 0.00 end as ‘high‘ from mst_class;"
增加分组，并取最大值
sql6 = "select name, max(case when grade = ‘primary‘ then rate else 0.00 end) as ‘primary‘ , max(case when grade = ‘middle‘ then rate else 0.00 end) as ‘middle‘ , max(case when grade = ‘high‘ then rate else 0.00 end) as ‘high‘ from mst_class group by name"
创建视图容器
sql7 = "create view class_grade as select name, max(case when grade = ‘primary‘ then rate else 0.00 end) as ‘primary‘ , max(case when grade = ‘middle‘ then rate else 0.00 end) as ‘middle‘ , max(case when grade = ‘high‘ then rate else 0.00 end) as ‘high‘ from mst_class group by name"
补充：
"""
1. IF(expr1,expr2,expr3)：如果第一个条件为True,则返回第二个参数，否则返回第三个
2. ifnull(expr1,expr2) 判断是否为空：假如第一个参数为null，则返回第二个参数；否则直接返回第一个参数的值
此处不上图了
"""
sql8 = "select id,name,if(gender=1,‘man‘,‘women‘) as sex from one_buyer" sql9 = "select id,name,case gender =1 when true then ‘man‘ else ‘women‘ end as sex from one_buyer" sql10 = "select id,name,ifnull(the_user,‘ttoia‘) as the_user from one_ser where id=32" sql11 = "select id,name,case when the_user is null then ‘ttoia‘ else the_user end as user from one_ser where id =32" sql12 = "insert into one_ser(name,price,num,is_sail) values(‘qiaolezi‘,456,89,1);" sql13 = "select id,the_user from one_ser where id>30"
待完善。。。
原文：https://www.cnblogs.com/little-sailor/p/15043899.html