计算多次连接的表中的值

Question

我在计算多次连接的表时遇到问题。

问题表：

+----+----------+
| id | question |
+----+----------+
|  1 | Foo?     |
+----+----------+

答案表：

+----+-------------+--------+
| id | question_id | choice |
+----+-------------+--------+
|  1 |           1 |      1 |
|  2 |           1 |      1 |
|  3 |           1 |      1 |
|  4 |           1 |      2 |
|  5 |           1 |      3 |
|  6 |           1 |      3 |
+----+-------------+--------+

预期结果：（

+----------+-------+-------+-------+
| question | num_1 | num_2 | num_3 |
+----------+-------+-------+-------+
| Foo?     |     3 |     1 |     2 |
+----------+-------+-------+-------+

失败的）查询及其结果：

SELECT
    q.question AS question,
    COUNT(a1.id) AS num_1,
    COUNT(a2.id) AS num_2,
    COUNT(a3.id) AS num_3
FROM
    question q
    LEFT JOIN answer a1 ON a1.question_id = q.id AND a1.choice = 1
    LEFT JOIN answer a2 ON a2.question_id = q.id AND a2.choice = 2
    LEFT JOIN answer a3 ON a3.question_id = q.id AND a3.choice = 3
GROUP BY
        q.id

+----------+-------+-------+-------+
| question | num_1 | num_2 | num_3 |
+----------+-------+-------+-------+
| Foo?     |     6 |     6 |     6 |
+----------+-------+-------+-------+

我不明白为什么会得到这个结果。 你能帮助我吗？

Answer 1

因为 choice = 1 给出 3 行，choice = 2 给出 1 行，choice = 3 给出 2 行，1 * 2 * 3 = 6。如果您删除group by并聚合并查看结果，那么结果应该很清楚。您可以使用

SELECT
    q.question AS question,
    COUNT(CASE WHEN a.choice = 1 THEN 1 END) AS num_1,
    COUNT(CASE WHEN a.choice = 2 THEN 1 END) AS num_2,
    COUNT(CASE WHEN a.choice = 3 THEN 1 END) AS num_3
FROM
    question q
    LEFT JOIN answer a ON a.question_id = q.id AND a.choice IN (1,2,3)
GROUP BY
        q.id,
        q.question

Answer 2

如果您在没有计数和分组的情况下运行查询，您将看到得到如下结果：

+------+------+------+------+
| q    | num1 | num2 | num3 |
+------+------+------+------+
| foo  |    1 |    4 |    5 |
| foo  |    1 |    4 |    6 |
| foo  |    2 |    4 |    5 |
| foo  |    2 |    4 |    6 |
| foo  |    3 |    4 |    5 |
| foo  |    3 |    4 |    6 |
+------+------+------+------+

正如预期的那样，有 6 行，因此每个别名字段将为您提供 6 的计数。Martin Smith 在上面得到了正确的答案。