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，非常简化语句，避免多次连接查询。

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 有两种用法，等值查询和非等值查询。

--等值查询
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

---

使用group by和case when 的两个例子

例子1

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

结果为：

姓名	语文	数学	英语
李四	70	80	90
张三	80	98	65

例子2

select 
      tname as '姓名',
      case
          when ttype='数学' then '理科'
          else '文科'
      end as '科别',
      sum(tscor) as '总分'
from testscore
group by 
      tname,
      case
          when ttype='数学' then '理科'
          else '文科'
      end

结果为：

姓名	科别	总分
李四	文科	160
李四	理科	80
张三	文科	145
张三	理科	98

---

Group By和Case When的结合使用

下面是数据源。

图一

现在要按B和C来组合分类，分别计算每种组合情况下D和E的比值，并且按照横轴为C，纵轴为B的形式展示这些比值。期望的结果是下面的。

图二

对应需要对两个字段进行组合分类，最容易想到的方式就是将B和C两个字段直接放到Group By后面就可以了，例如使用文章末尾贴出的SQL1语句，得到下图。

图三

但是这样得到的结果是将B、C的组合平铺来展示的。如果想要的是B和C分别作为一个表格的横轴和纵轴来展示（如图二），需要怎么办呢？这个时候最容易想到的是用临时表，在临时表之上二次查找，如文章末尾的SQL2所示。那么就可以得到下面的结果。

图四

SQL2解决了问题，但是思路比较粗狂，sql里面有没有高级的语法来实现这个功能呢？的确是有的，如SQL3所示，这里用到了case when。得到的结果如下。可以看到和SQL2得到的结果是一样的。

图五

但是其实case when貌似只能用在sum函数里面的时候，最后的Group By才对sum内部的数据才起作用。avg和count都不行，本质的原因不知道，但是avg和count都与计数有关，估计计数的函数内部是不敏感Group By后面的字段的。

sql列表

SQL1：

Select sum(D)/sum(E),B,C from test.newrain group by B,C

SQL2：

Select t1.C1,t2.C2,t1.Bi from
(select sum(D)/sum(E) as C1, B as Bi from test.newrain where C=1 group by B)t1,
(select sum(D)/sum(E) as C2, B as Bi from test.newrain  where C=2 group by B)t2
where t1.Bi=t2.Bi;

SQL3：

SELECT 
sum(case when C=1 then D else 0 end)/sum(case when C=1 then E else 0 end) as C1,
sum(case when C=2 then D else 0 end)/sum(case when C=2 then E else 0 end) as C2,B
from test.newrain
group by B

---

case when ,MAX(),group by

case when用与新增列

可以看到，这样无法只保留一行语文、数学、英语的成绩

为了保留一行成绩信息，引入group by 分组

这样就会出现只检索分组字段的第一条记录，这样就抹掉了其他两门课的成绩。

引入max()函数，改变这个难题

可以看出，每个人都取得了全部的成绩，因为在使用case when时，每个学生新增三列，只有一个是有值的，通过取最大值，分组后将最大值放到这一行记录上。