mysql连接同一个表的总和

Question

这是我的 table :

我正在尝试列出收入最高的用户列表(TOP 列表)。我使用了SUM(earnings)和GROUP BY username，但我也想SUMupline_earnings。

在上表中，user1是test的上线，因此test的所有收入都应算作user1> 收入。

这是我的代码:

SELECT COUNT (a.id) as total,
             (SUM(CASE WHEN b.upline=a.username THEN b.up_earnings ELSE 0 END)+SUM(a.earnings)) as tot_earnings,
             SUM(a.dls) as tot_dls,
             a.username
FROM logs a left join logs b ON a.username=b.upline
GROUP BY a.username ORDER BY tot_earnings DESC

但是它不起作用!看起来表 A 的结果重复了:http://i.stack.imgur.com/Mefht.png

欢迎任何帮助!

谢谢!

Answer 1

我认为您希望在执行加入之前按用户名聚合表。如果我明白你想要做什么，这可能是查询:

SELECT l.username, count(*) as total_uplines,
       coalesce(sum(b.up_earnings), 0) + l.earnings as tot_earnings,
       l.tot_dls
FROM (select l.username, sum(l.earnings) as earnings,
             sum(l.dls) as tot_dls
      from logs l
      group by l.username
     ) l left join
     logs b
     ON l.username = b.upline
GROUP BY l.username, l.earnings, l.tot_dls
ORDER BY tot_earnings DESC;