php - LEFT OUTER JOIN SUM 双倍问题

Question

表:购物

shop_id shop_name  shop_time
1   Brian  40
2   Brian   31
3   Tom   20
4   Brian   30

表:香蕉

banana_id  banana_amount  banana_person
1    1     Brian
2    1     Brian

我现在想要它打印:

姓名:汤姆 |时间:20 |香蕉:0
姓名:布赖恩 |时间:101 |香蕉:2

我使用了这段代码:

$result = dbquery("SELECT tz.*, tt.*,
SUM(shop_time) as shoptime,
count(banana_amount) as bananas

 FROM shopping tt
 LEFT OUTER JOIN bananas tz ON tt.shop_name=tz.banana_person
 GROUP by banana_person
LIMIT 40
");



while ($data5 = dbarray($result)) {

echo 'Name: '.$data5["shop_name"].' | Time: '.$data5["shoptime"].' | Bananas: '.$data5["bananas"].'<br>';


}

问题是我得到的是这个:

姓名:汤姆 |时间:20 |香蕉:0
姓名:布赖恩 |时间:202 |香蕉:6

我只是不知道如何解决这个问题。

Answer 1

问题是您正在构建两个表的叉积，将结果乘以对面表中的行数。要解决此问题，请首先计算派生表中一个表的聚合结果，并将此聚合结果连接到另一个表。

SELECT
    shop_name,
    shoptime,
    IFNULL(SUM(banana_amount), 0)
FROM (
    SELECT shop_name, SUM(shop_time) as shoptime
    FROM shopping
    GROUP BY shop_name
) tt
LEFT JOIN bananas tz ON tt.shop_name=tz.banana_person
GROUP BY shop_name