mysql 左连接使用sum结果不对_MySQL：带有JOIN的SUM()返回不正确的值

试试这个查询：

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

;

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

查看预期结果似乎查询应计算每个user_id的amount列的总和,但仅针对那些也在blocks表中的user_id,并且block.confirms的值小于520.

在这种情况下,简单连接(也称为左外连接)不起作用,因为块表可以包含同一user_id的许多记录,例如,仅返回user_id = 110的行的查询会得到以下结果：

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 |

+ ------- + ------------ + ----------- + ------------- +

straigh连接(和LEFT / RIGHT外连接)以这种方式工作,它从第一个连接表中获取每个记录,并将此记录(组合它)与来自另一个连接表的所有行配对,以满足连接条件.

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

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(金额)…. GROUP BY user_id,MySql将从上面的结果集中计算所有金额值的总和(8行* 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 |

+ ------------ + ----------------- +

正如你在这种情况下看到的那样,连接不会给我们带来预期的结果 – 我们需要一个名为半连接的东西 – 下面是半连接的不同变体,试试看：

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

;