mysql统计数据时sum if和count if的使用
-
- sum if 的使用
- count if 的使用
- ifnull 的使用:
- 示例:
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
需要了解更多数据库技术:mysql统计数据时sum if、count if、ifnull的使用,都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!
本文来自网络收集,不代表计算机技术网立场,如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址:https://www.ctvol.com/dtteaching/813579.html