mysql中sql的sum()和if()方法的用法
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_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
👁️ 阅读量：0
