sql语句中group by与case when的结合使用

╰半橙微兮° 2021-06-26 16:06 599阅读 0赞

有这么一个表`Price`，用来描述每天每种产品售出的金额，具体内容如表1.1所示，其中`date`字段代表日期，`type`字段表示产品类型，`price`字段表示产品售出金额。

<table> 
 <thead> 
  <tr> 
   <th>date</th> 
   <th>type</th> 
   <th>price</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>2020-07-13</td> 
   <td>0</td> 
   <td>33.30</td> 
  </tr> 
  <tr> 
   <td>2020-07-13</td> 
   <td>0</td> 
   <td>20.00</td> 
  </tr> 
  <tr> 
   <td>2020-07-12</td> 
   <td>1</td> 
   <td>21.00</td> 
  </tr> 
  <tr> 
   <td>2020-07-13</td> 
   <td>1</td> 
   <td>54.00</td> 
  </tr> 
  <tr> 
   <td>2020-07-12</td> 
   <td>1</td> 
   <td>11.00</td> 
  </tr> 
  <tr> 
   <td>2020-07-12</td> 
   <td>0</td> 
   <td>30.00</td> 
  </tr> 
 </tbody> 
</table>

现要做一个统计，统计每天每种产品类型的总售出金额，期望结果如下表所示，以日期`date`作为分组依据，分别计算每种产品类型在当天的售出总金额。

<table> 
 <thead> 
  <tr> 
   <th>date</th> 
   <th>type0_price</th> 
   <th>type1_price</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>2020-07-12</td> 
   <td>30.00</td> 
   <td>32.00</td> 
  </tr> 
  <tr> 
   <td>2020-07-13</td> 
   <td>53.30</td> 
   <td>54.00</td> 
  </tr> 
 </tbody> 
</table>

由需求可知，需要对日期和产品类型进行分组，再计算`price`的总和，可是如果我们直接用`group by`会产生怎样的效果？代码如下

select date,type,sum(price)
    from Price
    group by date,type;

得出的结果如下：

<table> 
 <thead> 
  <tr> 
   <th>date</th> 
   <th>typ</th> 
   <th>sum(price)</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>2020-07-12</td> 
   <td>0</td> 
   <td>30.00</td> 
  </tr> 
  <tr> 
   <td>2020-07-12</td> 
   <td>1</td> 
   <td>32.00</td> 
  </tr> 
  <tr> 
   <td>2020-07-13</td> 
   <td>0</td> 
   <td>53.30</td> 
  </tr> 
  <tr> 
   <td>2020-07-13</td> 
   <td>1</td> 
   <td>54.00</td> 
  </tr> 
 </tbody> 
</table>

可以看出计算结果和我们期望的是一致的，但是排列方式却有出入，直接求出的结果是按列排列的，而我们需要的是按行排列的。那么如何达到我们预期的效果呢？

一个方法是使用临时表，通过分别计算每种产品类型的每日售出总金额，再对每个结果进行汇总便能达到要求，具体代码如下：

select t1.date,t1.type0_price,t2.type1_price
    from
    (select date,sum(price) as type0_price from Price where type = 0 group by date) t1
    join
    (select date,sum(price) as type1_price from Price where type = 1 group by date) t2
    on t1.date = t2.date;

得到结果如下：

是我们预期所达到的效果，而且代码看着也较为简洁，可它存在着一个问题，当产品类型`type`的种类越来越多了怎么办？难道要继续`join`无数个表下去吗，当然可以是可以的，就是太费时费力了，不值得。因此我们需要一个更为简洁的方法。

另一种方法便是`group by` 与`case when` 的结合使用，直接看如下代码

select 
    date
    ,sum(case when type=0 then price end) as type0_price
    ,sum(case when type=1 then price end) as type1_price
    from Price 
    group by date;

得到的结果如下：

也是我们想要的结果，貌似代码量与第一种方法差不多，但是当`type`变多时，这种方法只需要继续加一个`sum`就可以实现，并且不需要创建额外的临时表进行操作，更加方便。

**总结：在sql语句中有使用到`group by` 时，`select`通常只能选择该进行`group by`的字段以及 `sum、count、avg、max、min`等聚合函数，经过测试，在这些聚合函数中，都可以使用`case when`进一步进行条件约束，作用域都为经过`group by`后的分组数据。**

--------------------

**sql查询，结合`group by`和`case when` ，子查询查询按照时间轴排序，某字段的标签新旧变化对比情况**

遇到这样一个业务，需要按照时间轴的顺序展示，标签的新旧变化情况，

表结构如下：  
![在这里插入图片描述][20210321205252449.png]  
sql语句如下：

SELECT
        tagName,
        CASE
            WHEN b.tagTime > (SELECT min(tagTime) FROM IP_gene_info c WHERE c.ip = b.ip AND c.tagName = b.tagName) 
            THEN
            (
                SELECT
                SUBSTRING_INDEX(GROUP_CONCAT(a.tagValue ORDER BY tagTime DESC),',',2)
                as tagValue
                FROM IP_gene_info a
                WHERE a.ip = b.ip
                AND   a.tagName = b.tagName
                AND   a.tagTime <=b.tagTime
                GROUP BY b.tagName
            )
            ELSE
                max(tagValue)
            END AS tagValue
    FROM
        IP_gene_info b
    WHERE
        b.ip = '117.18.237.29'
    GROUP BY
        b.tagName,
        b.tagTime
    ORDER BY
        tagTime

--------------------

## `group by case when`用法 ##

`select`的字段要和`group by`的一样，并且`group by`不能用别名

select
    case when left(tagName,4)='http' then 'http' when left(tagName,3) = '数据库' then '数据库' else 0 end as tagName,
    count(1)
    from IP_gene_info
    group by (case when left(tagName,4)='http' then 'http' when left(tagName,3) = '数据库' then '数据库'
    else 0
    end)

[20210321205252449.png]: /images/20210626/2bd0d091e8b547a59c38335e78ec97d8.png