Question

mysql sum()函数怎么得不到正确的结果啊！！！！

问题是这样的：一个充值表state=1表示充值成功的，一个消费表state=1表示消费成功的。我要列出充值成功的总值大于消费成功的总值所以用户id，充值总值，消费总值。语句如下：
SELECT a.userid,SUM(a.number) AS mai,SUM(i.total) AS hua
FROM account AS a,info AS i
WHERE a.state=1 AND a.userid=i.userid AND i.state=1
GROUP BY a.userid
HAVING SUM(a.number)>SUM(i.total)
但是充值总值，消费总值错误了。。。
比如充值4次成功。消费3次成功。
列出的充值额是真值*3次。消费额是真值*4次啊！！！！这不是我要的结果啊！！

求正确的语句！！！！！！

Answer 1

不能这么连接，你这样连接的话会出现重复数据，应该先分别聚合，然后再做连接，类似
select * from (select id,sum(money)money from a where state=1 group by id)a,
(select id,sum(money)money from b where state=1 group by id)b
where a.id=b.id and a.money>b.money

Answer 2

思路：列出充值成功的总值大于消费成功的总值用户id，充值总值

（SELECT a.userid,SUM(a.number) AS mai
FROM account AS a
WHERE a.state=1
GROUP BY a.userid）AA

-----消费总值
（SELECT userid,SUM(i.total) AS hua
FROM info AS i
WHERE i.state=1
GROUP BY i.userid）ii

SELECT * FROM AA,II WHERE AA.userid=II.userid AND AA.mai >II.hua

试下这个是否满足你的要求（AA,II使用上面的替代）

Answer 3

SELECT
a.userid,
a.mai,
i.hua
FROM
(SELECT userid, SUM(number) AS mai FROM account WHERE state=1 GROUP BY userid) AS a,
(SELECT userid, SUM(total) AS hua FROM info WHERE state=1 GROUP BY userid) AS i
WHERE
a.userid=i.userid
AND a.mai > i.hua