SUM 返回双精度值 [英] SUM returns double value
问题描述
几天前,有人帮我处理这个查询.我正在尝试获取我的数据库的前 10 条记录.问题是积分.当我在积分字段上使用 SUM 时,如果同一用户的记录超过 1 条,我会得到实际值的两倍......你知道我做错了什么吗?谢谢
A couple of days ago, somebody help me to approach this query. I'm trying to get the first 10 records of my DB. The problem is the points. When I use SUM on points field I get exactly the double of the real value in case of have more than 1 record of the same user... Do you know what am I doing wrong? Thank you
这段代码只返回用户的第一条记录(不会对同一用户的所有积分求和)
This is the code which returns just the first record of the user (doesn't SUM all the points of the same user)
SELECT *
FROM commerce
LEFT JOIN points ON points.id_com = commerce.id
LEFT JOIN comments ON comments.id_com = commerce.id AND (comments.validbycom = 1 AND comments.validbyus = 1)
LEFT JOIN winners ON winners.id_com = commerce.id
GROUP BY commerce.id
ORDER BY SUM(points) DESC, COUNT(comments.id_com) DESC, commerce.date ASC, COUNT(winners.id_com) DESC LIMIT 10
这是返回总数的两倍的代码
This is the code which return exactly the double of the total
SELECT SUM(points)
FROM commerce
LEFT JOIN points ON points.id_com = commerce.id
LEFT JOIN comments ON comments.id_com = commerce.id AND (comments.validbycom = 1 AND comments.validbyus = 1)
LEFT JOIN winners ON winners.id_com = commerce.id
GROUP BY commerce.id
ORDER BY SUM(points) DESC, COUNT(comments.id_com) DESC, commerce.date ASC, COUNT(winners.id_com) DESC LIMIT 10
推荐答案
据推测,points
列来自 points
表.额外的 join
会引入额外的行.所以,在加入之前尝试聚合:
Presumably, the points
column comes from the points
table. The extra join
s are introducing extra rows. So, try aggregating before doing the join:
SELECT sumpoints, commerce.id
FROM commerce LEFT JOIN
(select id_com, sum(points) as sumpoints
from points
group by id_com
) points
ON points.id_com = commerce.id LEFT JOIN
comments
ON comments.id_com = commerce.id AND (comments.validbycom = 1 AND comments.validbyus = 1) LEFT JOIN
winners
ON winners.id_com = commerce.id
GROUP BY commerce.id
ORDER BY sumpoints DESC, COUNT(comments.id_com) DESC, commerce.date ASC, COUNT(winners.id_com) DESC
LIMIT 10
这篇关于SUM 返回双精度值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!