MySQL数据库left join统计产生重复数据的解决办法

最近在用 MySQL 进行数据统计，left join 一不小心就会导致统计出来的数据有错误，在这里总结一下。

假设现在三张表：member（会员表），order（会员订单表），recharge（会员充值记录表），表的结构如下：

CREATE TABLE `member` (`id` bigint(20) unsigned NOT NULL COMMENT '主键 id',
  `is_deleted` int(11) DEFAULT NULL COMMENT '是否删除（0= 否；1= 是）',
  `mobile` varchar(20) DEFAULT NULL COMMENT '手机号',
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员信息';

CREATE TABLE `orders` (`id` bigint(20) unsigned NOT NULL COMMENT '主键 id',
  `pay_money` decimal(10,2) DEFAULT NULL COMMENT '付款金额',
  `order_no` varchar(50) DEFAULT NULL COMMENT '订单号',
  `member_id` bigint(20) DEFAULT NULL COMMENT '会员 id',
  `status` int(11) DEFAULT NULL COMMENT '状态 (0= 失败；1= 成功)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单信息';

CREATE TABLE `recharge` (`id` bigint(20) unsigned NOT NULL COMMENT '主键 id',
  `member_id` bigint(20) DEFAULT NULL COMMENT '会员 id',
  `recharge_money` decimal(10,2) DEFAULT NULL COMMENT '充值金额 (元)',
  `status` int(11) DEFAULT NULL COMMENT '充值状态（0= 待支付；1= 成功；2= 失败）',
  `order_no` varchar(50) DEFAULT NULL COMMENT '订单号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='充值信息';

会员订单表 order 和会员充值表都有一个关键的字段是 member_id（会员表的主键）。

现在有一个需求是：统计出每个会员的所有充值金额和订单消费金额。很多时候我们想当然的就这样写了：

SELECT
	IFNULL(sum( t2.recharge_money), 0 ) AS rechargeMoney,
	IFNULL(sum( t3.pay_money), 0 ) AS payMoney,
	t1.*
FROM
	member t1
	LEFT JOIN recharge t2 ON t1.id = t2.member_id
	AND t2.STATUS = 1
	LEFT JOIN orders t3 ON t1.id = t3.member_id
	AND t3.STATUS = 1
WHERE
	t1.is_deleted != 1
GROUP BY
	t1.id;

结果是统计出来了，但是呢，数据是错的，有些数据被重复统计了！

假如会员信息表、订单信息表和充值记录表是一对一的关系，那么，这样统计肯定是没错的。但问题是这是一对多的关系，所以就容易统计出重复数据，即使用 inner join 也是不行的。那怎么办呢？很简单，这样写就对了：

SELECT
	t2.recharge_money AS rechargeMoney,
	t3.pay_money AS payMoney,
	member.*
FROM
	member
	LEFT JOIN (SELECT sum( recharge_money) AS recharge_money, member_id FROM recharge WHERE STATUS = 1 GROUP BY member_id ) t2 ON member.id = t2.member_id
	LEFT JOIN (SELECT sum( pay_money) AS pay_money, member_id FROM orders WHERE STATUS = 1 GROUP BY member_id ) t3 ON member.id = t3.member_id
WHERE
	member.is_deleted != 1
GROUP BY member.id;

道理很简单，就是子查询里面先按照会员 id 分组统计了一遍，这样跟会员信息表就形成了一对一的关系，这样就不会错了。

查询出来的数据一对比，还真是不一样的。