MySQL对不同表中的多个列进行求和排序

Question

我有3个表:

用户

| id | name  |
|----|-------|
| 1  | One   |
| 2  | Two   |
| 3  | Three |

喜欢

| id | user_id | like  |
|----|---------|-------|
| 1  | 1       | 3     |
| 2  | 1       | 5     |
| 3  | 2       | 1     |
| 4  | 3       | 2     |

过渡语

| id | user_id | transaction |
|----|---------|-------------|
| 1  | 1       | -1          |
| 2  | 2       | 5           |
| 3  | 2       | -1          |
| 4  | 3       | 10          |

我需要得到点赞数。喜欢和交易。每个用户的翻译，然后按其结果排序。

我可以为用户和喜欢做这件事:

select users.*, sum(likes.like) as points
from `users`
inner join `likes` on `likes`.`user_id` = `users`.`id`
group by `users`.`id`
order by points desc

然后我像这样添加事务表:

select users.*, (sum(likes.like)+sum(transactions.`transaction`)) as points
from `users`
inner join `likes` on `likes`.`user_id` = `users`.`id`
inner join `transactions` on `transactions`.`user_id` = `users`.`id`
group by `users`.`id`
order by points desc

结果是错误的。

我期待看到:

| id | name  | points |
|----|-------|--------|
| 3  | Three | 12     |
| 1  | One   | 7      |
| 2  | Two   | 5      |

但是得到这个代替:

| id | name  | points |
|----|-------|--------|
| 3  | Three | 12     |
| 1  | One   | 6      |
| 2  | Two   | 5      |

那么，如何按喜欢的总和对用户排序。比如和事务，事务?

谢谢!

Answer 1

由于transactions和likes之间没有1对1的关系，我认为您需要使用子查询:

select users.*,
    (select sum(points) from likes where user_id = users.id) as points,
    (select sum(transaction) from transactions where user_id = users.id) as transactions
from users
order by points desc

在详细说明需求后更新:

select users.*,
    (select sum(points) from likes where user_id = users.id) +
    (select sum(transaction) from transactions where user_id = users.id) as points
from users
order by points desc