mysql - 从3张 table 获得总收入

我有users表和3个用户收入表。
我想做的是根据这三张表选出收入最高的前十名用户。
从昨天起我就一直在努力处理这个请求，我无法使它生效。
我想把这三张收入表加起来。
有什么帮助吗？

SELECT
    users.first_name,
    users.last_name,
    (select SUM(`value`) from `earnings1`  where users.id = earnings1.user)  as earnings1,
    (select SUM(`value`) from `earnings2`  where users.id = earnings2.user)  as earnings2,
    (select SUM(`value`) from `earnings3`  where users.id = earnings3.user)  as earnings3,
    (earnings1 + earnings2 + earnings3) as total
FROM users
GROUP BY users.id
ORDER BY total DESC
LIMIT 10

我现在得到的错误是：
“字段列表”中的未知列“earnings1”

最佳答案

这应该对你有用：

select first_name, last_name,
(earnings1 + earnings2 + earnings3) total from
(select users.id, users.first_name, users.last_name,
(select sum(`value`) from `earnings1` where users.id = earnings1.user) as earnings1,
(select sum(`value`) from `earnings2` where users.id = earnings2.user) as earnings2,
(select sum(`value`) from `earnings3` where users.id = earnings3.user) as earnings3
from users group by 1,2,3) t
order by 3 desc limit 10

t是从内部查询中给出结果集的名称。它有点类似于普通的表表达式，但并不精确。MySQL要求您为这个结果集命名。
1,2,3您可以使用用户1,2,3，也可以使用列名users.id、users.first_name、users.last_name作为分组列。1,2,3表示select语句中的第一、第二和第三列。

关于mysql - 从3张 table 获得总收入，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/41979348/