mysql - 从 3 个表中获取总收入

Question

我有 users 表和 3 个包含用户收入的表。
我想做的是根据这 3 个表选择收入最多的前 10 名用户。
从昨天开始，我一直在为这个请求苦苦挣扎，但我无法让它工作。
我想把这 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

我现在得到的错误是:

Unknown column 'earnings1' in 'field list'

Answer 1

这应该适合你:

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 语句中的第一、第二和第三列。