sql学习-case when

正大光明松鼠

于 2023-07-18 22:35:18 发布

阅读量84

点赞数

文章标签：学习

本文链接：https://blog.csdn.net/sunguizhu/article/details/131797472

版权

当需要对数据按条件进行统计或者处理时，需要用到case when.

1.case when的经典结构为：

select column1,column2,
case

when 条件1 then 结果1

when 条件2 then 结果2

else 结果3

END AS newColumn
from table;

2. case when 的下述两种表达方式是等效的：

case 
when tb1.os = 'android' then 'android'
when tb1.os = 'ios' then 'iPhone'
else 'PC'
end as os,

case tb1.os
when 'android' then 'android'
when 'ios' then 'iPhone'
else 'PC'
end as os,

3.应用举例：

3.1)增加一个新列

3.2）与group连用，并且增加多个新列

create table testScore    
(       
   tname varchar(30) null,    
   ttype varchar(10) null,    
   tscor int null   
);    
 
insert into testScore values ('张三','语文',80);   
insert into testScore values ('张三','数学',98);    
insert into testScore values ('张三','英语',65);    
insert into testScore values ('李四','语文',70);    
insert into testScore values ('李四','数学',80);    
insert into testScore values ('李四','英语',90);    
 
 
select
    tname as '姓名' ,     
    max(case ttype when '语文' then tscor else 0 end) '语文',     
    max(case ttype when '数学' then tscor else 0 end) '数学',     
    max(case ttype when '英语' then tscor else 0 end) '英语'     
from testScore     
group by tname

结果为：

3.3）case when 与sum联用

select depend_name,
             sum( case when dt>=date_add(CURRENT_DATE(),-1) then 1 else 0 end) as tb_call_times1,    
             sum( case when dt>=date_add(CURRENT_DATE(),-8) then 1 else 0 end) as tb_call_times7, 
             count(1) as tb_call_times30,
             concat(max(dt),' 00:00:00') as access_time
            from odm.odm_inn_etl_sched_trigger_depend_s_d where dt>add_months(CURRENT_DATE(),-1) and enable=1   
            group by depend_name

注：文档参考sql语言中group by和case when及sum(case when)的结合使用_group by case when_风神修罗使的博客-CSDN博客