SQL函数及数据透视表

1、If函数

语法：if(condition,true,false)
举例：
if（字段名>5,"对","错"）

2、条件函数

语法：
case
when 条件1 then "结果1"
when 条件2 then "结果2"
when 条件3 then "结果3"
......
else "默认值"
end as "新字段名"
举例：

select  
*,
case
    when a >= 100000 then "高投入"
    when a >= 50000 then "中投入"
    else "低投入"
end as type
from
    investmentlab;

3、数据透视表

case when与group by搭配使用，完成数据透视表的功能

select investdays,,
count(case when investment >= 100000 then "高收入" end) as "高投入",
count(case when investment >= 50000 and investment < 100000 then "中收入" end)  as "中收入",
count(case when investment  < 50000 then "低收入" end)  as "低收入"
from investmentlab
group by investdays

执行结果如下

image.png