MySQL:带有 JOIN 的 SUM() 返回不正确的值 [英] MySQL: SUM() with JOIN returns incorrect values

问题描述

我正在尝试为表中的每个用户获取 SUM()，但 MySQL 返回的值错误.

I am trying to fetch a SUM() for each user in a table, but MySQL is returning the wrong values.

这就是它的外观 (http://sqlfiddle.com/#!2/7b988/4/0)

user    amount
110     20.898319244385
114     43.144836425781
115     20.487638473511
116     26.07483291626
117     93.054000854492

但这就是它的外观(http://sqlfiddle.com/#!2/7b988/2/0)

user    amount
110     167.186554
114     129.434509
115     143.413469
116     208.598663
117     744.432007

这是我要运行的查询:

SELECT 
    blocks.user_id, 
    SUM(payout_history.amount) as amount
FROM blocks
LEFT JOIN payout_history
ON blocks.user_id = payout_history.user_id
WHERE confirms > 520
GROUP BY blocks.user_id

我做错了什么?

推荐答案

试试这个查询:

SELECT bl.user_id, SUM( ph.amount ) PAIDOUT
FROM (
   SELECT distinct blocks.user_id 
   FROM blocks
   WHERE confirms > 520
) bl
LEFT JOIN  payout_history ph
ON bl.user_id = ph.user_id
GROUP BY ph.user_id
;

SQLFiddle --> http://sqlfiddle.com/#!2/7b988/48

SQLFiddle --> http://sqlfiddle.com/#!2/7b988/48

--- 编辑 --- 解释查询是如何工作的(或者更确切地说，为什么您的查询不起作用)----

--- EDIT --- an explanation how the query works (or rather why your query doesn't work) ----

查看预期结果，查询似乎应该为每个 user_id 计算 amount 列的总和，但仅针对那些 user_id，它们也在 blocks 表中，并且 blocks.confirms 值大于 520.
在这种情况下，简单的连接(也称为左外连接)无法工作，因为 blocks 表可以包含同一 user_id 的许多记录，例如返回行的查询只有 user_id=110 给出以下结果:

Looking at expected results it seems that the query should calculate a sum of amount column for each user_id, but only for those user_id, that are also in the blocks table, and have a blocks.confirms value grather than 520.
A simple join (also left outer join) cannot work in this case, because the blocks table can contain many records for the same user_id, for example a query that returns rows for only user_id=110 gives the following results:

SELECT *
FROM blocks
WHERE confirms > 520
      AND user_id = 110;

+ ------- + ------------ + ----------- + ------------- +
| id      | user_id      | reward      | confirms      |
+ ------- + ------------ + ----------- + ------------- +
| 0       | 110          | 20.89832115 | 521           |
| 65174   | 110          | 3.80357075  | 698           |
| 65204   | 110          | 4.41933060  | 668           |
| 65218   | 110          | 4.69059801  | 654           |
| 65219   | 110          | 4.70222521  | 653           |
| 65230   | 110          | 4.82805490  | 642           |
| 65265   | 110          | 5.25058079  | 607           |
| 65316   | 110          | 6.17262650  | 556           |
+ ------- + ------------ + ----------- + ------------- +

直线连接(和左/右外连接)以这种方式工作，它从第一个连接表中获取每条记录，并将这条记录与另一个连接表中的所有行配对(组合)以满足连接条件.

在我们的例子中，左连接产生以下结果集:

The straigh join (and LEFT/RIGHT outer join) works in this way, that takes each record from the first joinded table, and pair this record (combine it) with all rows from the other joinded table thet meet the join condition.

In our case the left join produces a below resultset:

SELECT *
FROM blocks
LEFT JOIN payout_history
ON blocks.user_id = payout_history.user_id
WHERE confirms > 520
    AND blocks.user_id = 110;
+ ------- + ------- + ----------- + -------- + --- + ------- + ----------- +
| id      | user_id | reward      | confirms | id  | user_id | amount      |
+ ------- + ------- + ----------- + -------- + --- + ------- + ----------- +
| 0       | 110     | 20.89832115 | 521      | 1   | 110     | 20.898319   |
| 65174   | 110     | 3.80357075  | 698      | 1   | 110     | 20.898319   |
| 65204   | 110     | 4.41933060  | 668      | 1   | 110     | 20.898319   |
| 65218   | 110     | 4.69059801  | 654      | 1   | 110     | 20.898319   |
| 65219   | 110     | 4.70222521  | 653      | 1   | 110     | 20.898319   |
| 65230   | 110     | 4.82805490  | 642      | 1   | 110     | 20.898319   |
| 65265   | 110     | 5.25058079  | 607      | 1   | 110     | 20.898319   |
| 65316   | 110     | 6.17262650  | 556      | 1   | 110     | 20.898319   |
+ ------- + ------- + ----------- + -------- + --- + ------- + ----------- +

现在如果我们添加 SUM(amount) .... GROUP BY user_id，MySql 将计算上述结果集中所有 amount 值的总和(8 行* 20.898 = ~ 167.184)

and now if we add SUM( amount ) .... GROUP BY user_id, MySql will calucate a sum of all amount values from the above resultset ( 8 rows * 20.898 = ~ 167.184 )

SELECT blocks.user_id, sum( amount)
FROM blocks
LEFT JOIN payout_history
ON blocks.user_id = payout_history.user_id
WHERE confirms > 520
    AND blocks.user_id = 110
GROUP BY blocks.user_id;
+ ------------ + ----------------- +
| user_id      | sum( amount)      |
+ ------------ + ----------------- +
| 110          | 167.186554        |
+ ------------ + ----------------- +

正如你在这种情况下看到的，连接并没有给我们想要的结果——我们需要一个名为 a semi join 的东西——下面是半连接的不同变体，试一试:

As you see in this case the join doesn't give us desired results - we need something named a semi join - below are different variants of semi joins, give them a try:

SELECT bl.user_id, SUM( ph.amount ) PAIDOUT
FROM (
   SELECT distinct blocks.user_id 
   FROM blocks
   WHERE confirms > 520
) bl
LEFT JOIN  payout_history ph
ON bl.user_id = ph.user_id
GROUP BY ph.user_id
;


SELECT ph.user_id, SUM( ph.amount ) PAIDOUT
FROM payout_history ph
WHERE ph.user_id IN (
     SELECT user_id FROM blocks
     WHERE confirms > 520
  )
GROUP BY ph.user_id
;

SELECT ph.user_id, SUM( ph.amount ) PAIDOUT
FROM payout_history ph
WHERE EXISTS (
     SELECT 1 FROM blocks bl
     WHERE bl.user_id = ph.user_id
        AND bl.confirms > 520
  )
GROUP BY ph.user_id
;

这篇关于MySQL:带有 JOIN 的 SUM() 返回不正确的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！