mysql中sql的sum()和if()方法的用法

if()方法

IF(字段='某一值', yes就为xxx或另一字段的值,no就为xxx或另一字段的值)
-- 类似与三目运算表达式 3>2?'yes':'no'

sum()方法

sum()方法，很简单，就是求和，符合条件的求和
sum( IF ( tr.action = 'add', tr.action_money, 0 ) ) AS addMoney
-- 这句sql的意思就是查询出的所有值，所有满足action='add'的值进行相加

示例sql

SELECT
	tr.district_id,
	t.name,
	sum( IF ( tr.action = 'add', tr.action_money, 0 ) ) AS addMoney,
	sum( IF ( tr.action = 'clear', tr.action_money, 0 ) ) AS withdrawMoney,
	sum( IF ( tr.action = 'add', tr.action_money, 0 ) + IF ( tr.action = 'clear', tr.action_money, 0 ) ) AS addAll,
	sum( IF ( tr.action = 'add' AND tr.payment_way = 'weixin', tr.action_money, 0 ) ) AS weixinAddMoney,
	sum( IF ( tr.action = 'reduce', tr.action_money, 0 ) ) AS reduceMoney,
	sum( IF ( tr.action = 'cancel', tr.action_money, 0 ) ) AS cancelMoney,
	sum( IF ( tr.action = 'reduce', tr.action_money, 0 ) + IF ( tr.action = 'cancel', tr.action_money, 0 ) ) AS reduceAll 
FROM
	tb_trade_record tr
	LEFT JOIN tb_org_district t ON t.id = tr.district_id 
WHERE
	tr.consumer_type = 'patient' 
	AND tr.org_id = '104' 
	AND tr.is_deleted = 0 
	AND tr.is_enable = 1 
	and tr.zone_id =1
	AND DATE_FORMAT( tr.action_date, '%Y-%m-%d %T' ) >= DATE_FORMAT( '2019-01-01 17:23:56', '%Y-%m-%d %T' ) 
	AND DATE_FORMAT( tr.action_date, '%Y-%m-%d %T' ) <= DATE_FORMAT( '2020-05-06 17:23:56', '%Y-%m-%d %T' ) 
GROUP BY
	tr.district_id
order by t.code asc