加入投票表并汇总所有投票 [英] Join votes table and sum all votes

问题描述

我有两个桌子.其中一个包含引号，另一个列出每个引号的所有给定票数(+1或-1).出于演示目的，我对两个表进行了简化:

I've got two tables. One of them contains quotes and the other one lists all given votes (either +1 or -1) for each quote. For demonstration purposes I've made simplified versions of the two tables:

+----+-----------------------------------------------------------------------+
| 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查询的结果将如下所示:

I'd like to list all quotes on my site and show the respective vote count besides. At first, the SQL query should read all quotes and afterwards join the votes table. However, it should finally list the sum of all votes for each quote. The result of the SQL query will therefore look as follows:

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

SQL查询的外观如前所述吗?

How does the SQL query look like that does the previously described?

推荐答案

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列是主键(它们对于每个记录都是唯一的).

Assuming id columns are primary keys (they are unique for each record).

这篇关于加入投票表并汇总所有投票的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！