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

admin管理员组

文章数量:1794759

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

mysql中sql的sum（）和if（）方法的用法）

• if()方法
• sum()方法
• 示例sql

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

本文标签： 方法mySQLSQLsum