sql语言中group by和case when及sum(case when)的结合使用

sql语言中group by和case when及sum(case when)的结合使用

group by和case when结合使用

如下示代码块，group by 按case when得到的列分组计算，且select时，select 中的case when 与group by中保持一致。

 select case when substr(upper(depend_name),1,4)='JCW_' then lower(substr(depend_name,5,length(depend_name))) else lower(depend_name)end as table_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 case when substr(upper(depend_name),1,4)='JCW_' then lower(substr(depend_name,5,length(depend_name))) else lower(depend_name)end 

sum和case when结合使用

在sum()中使用case when，非常简化语句，避免多次连接查询。

```sql
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 

复习case when的使用

case when 有两种用法，等值查询和非等值查询。

```sql 等值查询
select 
case sex 
when 1 then 'male'
when 2 then 'female'
else 'mixed'
end as sexual,sex
from tbl

select 
case  
when price>100 then 'expensive'
when price<=100 then 'cheap'
else '异常值'
end as price，价格分类
from tbl