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