mysql - 加入投票表并对所有投票求和

Question

我有两张 table 。其中一个包含引用，另一个列出每个引用的所有给定投票(+1 或 -1)。出于演示目的，我制作了两个表的简化版本:

行情

+----+-----------------------------------------------------------------------+
| ID | quote                                                                 |
+----+-----------------------------------------------------------------------+
|  1 | If you stare into the Abyss long enough the Abyss stares back at you. |
|  2 | Don't cry because it's over. Smile because it happened.               |
|  3 | Those that fail to learn from history, are doomed to repeat it.       |
|  4 | Find a job you love and you'll never work a day in your life.         |
+----+-----------------------------------------------------------------------+

投票数

+----+-------+------+
| ID | quote | vote |
+----+-------+------+
|  1 |     1 |   -1 |
|  2 |     1 |   -1 |
|  3 |     3 |    1 |
|  4 |     3 |   -1 |
|  5 |     3 |    1 |
|  6 |     3 |   -1 |
|  7 |     4 |    1 |
|  8 |     4 |    1 |
|  9 |     4 |    1 |
+----+-------+------+

我想列出我网站上的所有报价，并显示相应的投票数。首先，SQL 查询应该读取所​​有报价，然后加入投票表。但是，它最终应该列出每个引用的所有投票的总和。因此，SQL 查询的结果如下所示:

+----+-----------------+------+
| ID | quote           | vote |
+----+-----------------+------+
|  1 | If you stare... |   -2 |
|  2 | Don't cry...    | NULL |
|  3 | Those that...   |    0 |
|  4 | Find a job...   |    3 |
+----+-----------------+------+

SQL 查询看起来像前面描述的那样吗？

Answer 1

SELECT
    `quotes`.`id` as `ID`,
    `quote`.`quote` as `quote`,
    SUM(`votes`.`vote`) AS `vote`
FROM  `quotes`
    LEFT JOIN `votes`
        ON `quotes`.`id` = `votes`.`quote`
GROUP BY `quotes`.`id`

应该可以解决问题。

假设 id 列是主键(它们对于每条记录都是唯一的)。