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 joins 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屋！