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