我要评论
mysql统计数据时sum if和count if的使用
sum if 的使用
条件格式：sum(if(条件,字段名或固定值,0))
注解：sum是求和函数，条件为true时，列值（字段名）或固定值累加求和，条件为false时用0累加
1.单条件判断格式，sum(if(条件,字段名或固定值,0))
2.多条件判断格式，sum(if(条件 AND 条件 AND 条件,字段名或固定值,0))
注解：固定值如果为1时，等价于count if计数，固定值如果为2时，结果=计数值 × 2
count if 的使用
条件格式：
1.统计总数，count(if(条件,true,null))
2.某字段去重统计总数，count(distinct 去重字段名,if(条件,true,null))
ifnull 的使用：
格式：IFNULL(expression, alt_value)
注解：expression 表达式为 NULL 时返回alt_value
常规用法：SUM 累加的数据如果不存在则返回null，所以配合IFNULL可实现null转为0，代码层则无需做null转0的操作了。
示例：
mysql统计数据的写法一：
SELECT
COUNT(*) 'ORDER_COUNT'
, SUM(IF(true, o.amount, 0)) 'ORDER_SUM_AMOUNT'
, SUM(IF(o.`status` = 0, 1, 0)) 'PENDING_PAYMENT'
, SUM(IF(o.`status` = 1, 1, 0)) 'PENDING_REVIEW'
, SUM(IF(o.`status` = 2, 1, 0)) 'PENDING_SHIPMENT'
, SUM(IF(o.`status` = 3, 1, 0)) 'SHIPPED'
, SUM(IF(o.`status` = 4, 1, 0)) 'RECEIVED'
, SUM(IF(o.`status` = 5, 1, 0)) 'COMPLETED'
, SUM(IF(o.`status` = 6, 1, 0)) 'FAILED'
, SUM(IF(o.`status` = 7, 1, 0)) 'CANCELED'
, SUM(IF(o.`status` = 8, 1, 0)) 'DENIED'
, SUM(IF(o.`status` in (0,1) and o.expire is not null and o.expire < NOW(), 1, 0)) 'EXPIRED'
, SUM(IF(o.`status` in (0,1) and o.createdDate >= CURRENT_DATE and o.expire is not null and o.expire < NOW(), 1, 0)) 'TODAY_EXPIRED'
, SUM(IF(o.`status` = 0 and (o.expire is null or o.expire > NOW()), 1, 0)) 'PENDING_PAYMENT_ORDER_COUNT'
, SUM(IF(o.`status` in (1,2) and (o.expire is null or o.expire > NOW()), 1, 0)) 'PENDING_SHIPMENT_ORDER_COUNT'
, SUM(IF(o.`status` in (4,5) and (o.isReviewed is null or o.isReviewed = 0), 1, 0)) 'PENDING_REVIEW_COUNT'
FROM Orders o
WHERE o.member_id = 66666
mysql统计数据的写法二：
SELECT
COUNT(*) 'ORDER_SUM'
, IFNULL(SUM(o.amount), 0) 'ORDER_SUM_AMOUNT'
, COUNT(IF(o.`status` = 0, true, null)) 'PENDING_PAYMENT'
, COUNT(IF(o.`status` = 1, true, null)) 'PENDING_REVIEW'
, COUNT(IF(o.`status` = 2, true, null)) 'PENDING_SHIPMENT'
, COUNT(IF(o.`status` = 3, true, null)) 'SHIPPED'
, COUNT(IF(o.`status` = 4, true, null)) 'RECEIVED'
, COUNT(IF(o.`status` = 5, true, null)) 'COMPLETED'
, COUNT(IF(o.`status` = 6, true, null)) 'FAILED'
, COUNT(IF(o.`status` = 7, true, null)) 'CANCELED'
, COUNT(IF(o.`status` = 8, true, null)) 'DENIED'
, COUNT(IF(o.`status` in (0,1) and o.expire is not null and o.expire < NOW(), true, null)) 'EXPIRED'
, COUNT(IF(o.`status` in (0,1) and o.createdDate >= CURRENT_DATE and o.expire is not null and o.expire < NOW(), true, null)) 'TODAY_EXPIRED'
, COUNT(IF(o.`status` = 0 and (o.expire is null or o.expire > NOW()), true, null)) 'PENDING_PAYMENT_ORDER_COUNT'
, COUNT(IF(o.`status` in (1,2) and (o.expire is null or o.expire > NOW()), true, null)) 'PENDING_SHIPMENT_ORDER_COUNT'
, COUNT(IF(o.`status` in (4,5) and (o.isReviewed is null or o.isReviewed = 0), true, null)) 'PENDING_REVIEW_COUNT'
FROM Orders o
WHERE o.member_id = 66666
写法一和写法二的结果一样，但是写法二列结果为null时返回0
本文地址：https://blog.csdn.net/zhoujinjinjin/article/details/109471054
您可能感兴趣的文章:
- 在CentOS7上安装MySQL5.7-YUM源方式
- MySQL从删库到跑路（四）——MySQL数据库创建实例
- mysql-utilities 出现 No module named utilities
- [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause 的问题 MySQL
- macOS，安装+配置+激活：MySQL8.0 + Navicat Premium12
- CentOS7.4 源码安装MySQL8.0的教程详解
- mysql-community-common(x86-64))=5.7.9isneededbymysql-community-libs-4报错问题解决
如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！
网友评论