MySQL中使用sum(if()) 或 sum(case)实现条件式的汇总字段值

admin管理员组

文章数量:1794759

MySQL中使用sum(if()) 或 sum(case)实现条件式的汇总字段值

一、前言

最近需要做一个营销商品的销售报表，其中有那么一个逻辑：

1> 比如交易正向单买了2连个skuA，逆向单退了1个skuA；

• 如果正向单和逆向单在同一天生成，则只展示一条记录，记录内容为：正向单1个skuA。
• 如果正向单和逆向单不在同一天生成，则展示两条记录，记录内容分别为：正向单2个skuA、逆向单-1个skuA。

二、sum() / if() / case函数介绍 1、if()

在MySQL中if()函数的用法类似于java中的三目表达式，具体语法如下：

IF(字段=‘某一值’, yes就为xxx或另一字段的值,no就为xxx或另一字段的值)

例如：IF(expr1,expr2,expr3)

意思为：如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。

• IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。

2、CASE WHEN THEN

MySQL的case和java中的switch…case类似，但是它有两种语法：

1> 一种是像Java的switch … case一样，在CASE 后面跟某一列、WHEN中输入列值

具体语法如下：

CASE case_value WHEN when_value1 THEN statement_list1 WHEN when_value2 THEN statement_list2 ELSE statement_list3 END

示例：

CASE flag WHEN 1 THEN -1 WHEN 0 THEN 1 ELSE 0 END

解析：

• 针对flag字段做判断，如果flag=1，则返回-1；如果flag=0，则返回1，否者其余情况返回0。

2> 第二种是CASE后面不跟任何东西、直接在WHEN中写表达式

具体语法如下：

CASE WHEN when_expression1 THEN statement_list1 WHEN when_expression1 THEN statement_list2 ELSE statement_list3 END

示例：

CASE WHEN flag=1 THEN 0 WHEN flag=0 THEN 1 ELSE 0 END

解析：

• 针对flag字段做判断，如果flag=1，则返回-1；如果flag=0，则返回1，否者其余情况返回0。

3、sum()

mysql的sum()函数把某一列的值全部相加，可以做到去重，具体语法如下：

SUM([DISTINCT] expr)

解释：

• 返回expr 的总数。 若返回集合中无任何行，则 SUM() 返回NULL。DISTINCT 关键词用于求得expr 不同值的总和。

三、sum() / if() / CASE聚合使用 0、表结构

1、sum(if()) select qty, businessDate, flag, sum(IF(flag = 1, 0 - reverseQty, qty)) as sum from test_baobiao GROUP BY tradeOrderLineBO, businessDate;

逻辑为：

• 如果flag为1，则返回reverseQty字段的相反值，否者返回qty字段的值，并且按照tradeOrderLineBO、businessDate分组，最终对reverseQty和qty做一个分组汇总操作。

运行结果：

2、sum(case) select tradeOrderLineBO, qty, businessDate, flag, sum( CASE WHEN flag = 1 THEN 0 - reverseQty WHEN flag = 0 THEN qty ELSE 0 END ) as sum from test_baobiao GROUP BY tradeOrderLineBO, businessDate;

逻辑为：

• 如果flag为1，则返回reverseQty字段的相反值；
• 如果flag为0，返回qty字段的值，否者返回0；并且按照tradeOrderLineBO、businessDate分组，最终对reverseQty和qty做一个分组汇总操作。

运行结果：

这里是按照时间字段做精确匹配进行分组的，假如我想实现前言中的需求，就需要对businessDate字段做一个截取，只取前10位，这里可以使用到LEFT()函数。

SQL如下：

select qty, businessDate, flag, sum(IF(flag = 1, 0 - reverseQty, qty)) as sum from test_baobiao GROUP BY tradeOrderLineBO, LEFT(businessDate,10);

本文标签： 条件式字段mySQLsumCASE