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

发布网友发布时间：2022-04-26 09:57

共3个回答

热心网友时间：2022-04-10 13:28

不能这么连接，你这样连接的话会出现重复数据，应该先分别聚合，然后再做连接，类似
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

热心网友时间：2022-04-10 14:46

思路：列出充值成功的总值大于消费成功的总值用户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使用上面的替代）

热心网友时间：2022-04-10 16:21

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