MYSQL 双连接表和不准确的计数/求和

Question

我正在尝试生成一份报告，显示排名靠前的推荐人以及他们推荐的人产生了多少收入。

这是我的表格的缩写版本:

Users Table
------------------
id
referral_user_id (references another record in the users table)
username
firstname
lastname

Transactions Table
-------------------
id
user_id
amount

我尝试使用以下查询:

SELECT 
    users.id, 
    users.firstname, 
    users.lastname, 
    users.username, 
    COUNT(referrals.id) referrals, 
    SUM(transactions.amount) income
FROM `users`
LEFT JOIN `users` AS `referrals` ON `users`.`id` = `referrals`.`referral_user_id`
LEFT JOIN `transactions` ON `referrals`.`id` = `transactions`.`user_id` 
GROUP BY `users`.`id`
ORDER BY `income` desc

我得到以下结果:

ID  |   username    |   Name    |   Referrals   |   Revenue From Referrals
----------------------------------------------------------------------------
1       jj              Jason       136             6536.30
2       jim             Jim         206             2957.75
3       tim             Tim         67              2712.10

唯一的问题是数字不相加。当我执行查询 SELECT COUNT(id) FROM users WHERE Referral_user_id = 1 时，我得到的计数为 77，当我执行 SELECT COUNT(id) FROM users WHERE Referral_user_id = 2，我得到的计数是 161。

当我删除查询的交易/收入部分并执行以下操作时:

SELECT 
    users.id, 
    users.firstname, 
    users.lastname, 
    users.username, 
    COUNT(referrals.id) referrals
FROM `users`
LEFT JOIN `users` AS `referrals` ON `users`.`id` = `referrals`.`referral_user_id`
GROUP BY `users`.`id`
ORDER BY `referrals` desc

我得到了正确的推荐计数。谁能向我解释一下我缺少什么吗？

Answer 1

您可能对 SQL 中聚合的工作方式做出了错误的假设。

假设您像初始查询一样加入，没有 GROUP BY 和聚合，它可能会给出以下数据:

用户 1 与 [2,3] 关联，金额为 [100,200]，加入时我们会看到 4 条记录

ID  |   username    |   Name    |   referral_user_id    |   Amount
----------------------------------------------------------------------------
1       jj              Jason       2                       100
1       jj              Jason       3                       200
1       jj              Jason       3                       100
1       jj              Jason       2                       200

使用您的查询将对上面的结果进行分组:

• 导致计数为 4 而不是 2
• 结果是 600 而不是 300

请注意，输入 COUNT(referrals.id) 没有帮助，例如 COUNT(1) 会给出相同的结果。

对于引用，当且仅当您没有多次计算 a 和 b 之间的关系时，您可以使用 COUNT(DISTINCT Referrals.id)。

否则，对于金额，您肯定应该使用子查询来解决它。