mysql - 如何对 MySQL 中的另一个表进行 ORDER BY CALCULATED SUM

Question

例如，我有 2 个表“meta”和“log”

在元表中:

| type | score |
|------|-------|
| a    | 1     |
| b    | 2     |
| c    | 3     |

在日志表中:

| log_id | log_type | object_id |
|--------|----------|-----------|
| 1      | a        | 13        | 
| 2      | b        | 13        |
| 3      | a        | 14        |
| 4      | c        | 14        |
| 5      | b        | 15        |
| 6      | c        | 15        |

所以我们知道:

object 13 got score: a+b = 3
object 14 got score: a+c = 4
object 15 got score: b+c = 5

我想按对象id查询日志表，按对象分数总和排序，可以吗？

Answer 1

select log.object_id, sum(meta.score)
from log
left join meta on meta.type = log.log_type
group by log.object_id
order by sum(meta.score) desc