从调查表中选择并计票，其中并非所有选项都有票

Question

我正在努力完成一项可能非常简单的任务，但我很难弄清楚。我有两个数据库表：

options_table

option_id | option_name | question_id |
    1     |   opt one   |      4      |
    2     |   opt two   |      4      |
    3     |   opt three |      4      |
    4     |   opt four  |      4      |

和 votes_table

question_id | survey_id | option_id |
    4       |    1      |     1     |
    4       |    1      |     2     |
    4       |    1      |     2     |
    4       |    1      |     1     |
    4       |    1      |     3     |
    4       |    1      |     2     |

在这个例子中，我们对选项 1 投了 2 票，对选项 2 投了 3 票，对选项 3 投了 1 票，但对选项 4 没有投票。 我想检索所有选项名称及其相对计数，但如果没有投票，我无法获得该选项。我确信我忽略了一些事情，工作日结束了，我一定很累了。 我尝试过类似的方法：

   SELECT o.option_name, count(*) as cnt 
   FROM votes_table AS v 
   JOIN options_table AS o 
   ON o.option_id = v.option_id 
   WHERE v.survey_id = 1 
   GROUP BY o.option_name

但我只是得到

option_name | option_id | cnt
 opt one    |   1       |  2
 opt two    |   2       |  3
 opt three  |   3       |  1

如何获得“选择四”？这样我就可以正确显示所有调查结果，包括没有人投票的选项。我也尝试过使用 OUTER JOIN，但没有成功。

我很羞愧，因为这可能听起来是一个愚蠢的问题，但我在数据库和关系方面还有很多东西需要学习

Answer 1

使用 LEFT 而不是 INNER 连接 - 将包含所有选项的表放在左侧，将缺少选项的表放在右侧。

将使用右表中的列的任何条件（例如 v.survey_id = 1 条件）从 WHERE 移至 ON 子句。

使用 COUNT(RightTable.column) 而不是 COUNT(*)，因此未投票的选项会得到 0：

SELECT o.question_id
     , o.option_name
     , COUNT(v.option_id) as cnt 
FROM 
    options_table AS o  
  LEFT JOIN 
    votes_table AS v
      ON  v.option_id = o.option_id 
      AND v.question_id = o.question_id
      AND v.survey_id = 1 
WHERE o.question_id = 4            --- if you want results for one question only 
GROUP BY o.question_id
       , o.option_id

我认为 (option_id, Question_id) 是 options 表的主（或唯一）键。这就是为什么您需要对它们进行GROUP BY，或者对WHERE o.question_id和GROUP BY o.option_id进行分组。

否则，如果您仅GROUP BY o.option_id，则每个选项的查询都会对使用此选项投票的所有问题进行计票。

Answer 2

SELECT 
  o.option_name, 
  ( SELECT 
      count(*) 
    FROM 
      votes_table 
    WHERE  
      v.survey_id = 1
    AND
      v.question_id = o.question_id
    AND
      v.otion_id = o.option_id 
  ) as cnt 
options_table AS o;

Answer 3

您可以使用 LEFT JOIN 来代替使用 INNER JOIN (JOIN) 来实现此目的

 SELECT o.option_name, count(*) as cnt 
 FROM options_table AS o  
 LEFT JOIN votes_table AS v
 ON o.option_id = v.option_id 
 WHERE v.survey_id = 1 
 GROUP BY o.option_name;

Answer 4

尝试一下

SELECT o.option_name, count(*) AS cnt 
FROM options_table o 
LEFT JOIN votes_table v ON o.option_id = v.option_id 
WHERE v.survey_id = 1 
GROUP BY o.option_name

如果您使用INNER JOIN，您只会获得两个表中都有相应项目的记录。