mysql - 如何为 MySQL 中的每个不同项目选择随机结果

Question

我目前有以下表格

• 问题
• 答案
• 用户
• 投票

示例表集是这样的题表:

question_id  | question
1            | Question #1
2            | Question #2
3            | Question #3
4            | Question #4
5            | Question #5

答案表:

answer_id      | answer      | user_id      | question_id
1              | Answer #1   | 2            | 1
2              | Answer #2   | 2            | 2
3              | Answer #3   | 3            | 2
4              | Answer #4   | 1            | 3
5              | Answer #6   | 1            | 2
6              | Answer #7   | 2            | 5
7              | Answer #8   | 1            | 5
8              | Answer #9   | 3            | 1
9              | Answer #10  | 2            | 5

用户表:

user_id   | name 
1         | user #1
2         | user #2
3         | user #3

投票表:

vote_id   | vote_type   | user_id  | answer_id
1         | 1           | 1        | 1
2         | 0           | 3        | 1
3         | 0           | 3        | 8
4         | 1           | 2        | 3
5         | 1           | 2        | 4
6         | 0           | 1        | 4
7         | 1           | 3        | 3
8         | 0           | 1        | 5
9         | 1           | 1        | 2

我需要帮助来编写一个查询，该查询将允许我显示每个项目一次(即使是那些没有答案的项目)以及随机答案和它收到的总票数。

示例结果:

[Question 1 | Answer_id = 8   | user_id = 3   | total votes: 1  ]
[Question 2 | Answer_id = 3   | user_id = 3   | total votes: 2  ]
[Question 3 | Answer_id = 4   | user_id = 1   | total votes: 2  ]
[Question 4 | Answer_id = n/a | user_id = n/a | total votes: n/a]
[Question 5 | Answer_id = 10  | user_id = 2   | total votes: 0  ]

如果我要刷新查询，它会输出相同的问题和随机答案(如果有不同的答案可用)。

感谢任何帮助。

谢谢。

Answer 1

您可以试试这个解决方案:

SELECT a.question, a.answer_id, a.user_id, a.totalvotes
FROM
(
    SELECT a.question, b.answer_id, c.user_id, COUNT(d.vote_id) AS totalvotes
    FROM questions a
    LEFT JOIN answers b ON a.question_id = b.question_id
    LEFT JOIN users c ON b.user_id = c.user_id
    LEFT JOIN votes d ON b.answer_id = d.answer_id
    GROUP BY a.question, b.answer_id
    ORDER BY RAND()
) a
GROUP BY a.question

---

SQLFiddle Demo

^ 你可以一直点击“运行 SQL”按钮，不同的答案会出现。