MYSQL根据2个不同表的2列排列用户

Question

我正在寻找一个可能的解决方案，将给我从users表谁是赚取的金额和推荐他们从2表订购的TOP 10用户。从同一个users表中使用inviter_id列和amount列从另一个名为transactions的表中引用的总次数。

这是users和transactions表的表模式。

usersTABLE

id     inviter_id
1      1
2      1
3      1
4      1
5      2
6      3
7      5
8      6
9      1
10     3
11     9
12     1
13     5
14     7
15     11

解释:id代表用户的唯一ID,inviter_id代表邀请用户的ID。

transactionsTABLE

id     receiver_id amount
1      1           200
2      1           100
3      1           50
4      2           10
5      3           400
6      4           200
7      5           100
8      6           50
9      7           100
10     8           50
11     9           50
12     10          100 
13     11          400
14     1           200
15     2           100
16     1           50
17     1           10
18     4           500

这里receiver_id是users表中的用户。

期望输出:

user_id     referrals     earned
1           6             610
2           1             110
3           2             400
4           0             700
5           2             100
6           1             50
7           1             100
8           0             50
9           1             50
10          0             100
11          1             400
12          0             0
13          0             0
14          0             0
15          0             0

解释:我需要每个用户所做的推荐数以及该用户所做的收益。

奖金:我需要根据谁有最高的推荐和收入排序的输出表。

到目前为止我所尝试的:由于我使用codeigniter查询生成器，以下是我的代码。

$this->db->select('u.id, AS user_id, IF(COUNT(p.id) IS NULL, 0, COUNT(p.id)) AS referrals, IF(SUM(m.amount) IS NULL, 0, SUM(m.amount)) AS earned');
$this->db->from('users u');
$this->db->join('transactions m', "u.id = m.receiver_id",'LEFT');
$this->db->join('users p', "u.id = p.inviter_id",'LEFT');
$this->db->group_by('u.id');
$this->db->limit(10);
$this->db->order_by('referrals', 'DESC');
$this->db->order_by('earned', 'DESC');
$query = $this->db->get();
$row = $query->result();

当我加入表时，我得到了错误的推荐和收益值，COUNT和SUM给了我加入的行数。

Answer 1

如果我理解正确的话，您需要聚合这两个表。要保留所有id，请在连接前使用left join并聚合表:

select u.id, coalesce(ui.referrals, 0), coalesce(t.earned, 0) as earned
from users u left join
(select u.inviter_id, count(*) as referrals
from users u
group by u.inviter_id
) ui 
on ui.inviter_id = u.id left join
(select receiver_id, sum(amount) as earned
from transactions t
group by t.receiver_id
) t
on t.receiver_id = u.inviter_id
group by u.id
order by referrals desc, earnings desc;