MySQL选择一些随机的行，再加上一个特定的行 [英] Mysql Select some random rows and plus one specific row

问题描述

我有一个测验系统，其中的问题有多个选择.

I have a quiz system which questions have multiple choises.

我想显示正确的答案以及另外四个错误的选择.总共我必须有五个选择.

I want to show the right answer plus four more wrong choices. Totally I must have five choices.

表格选择

id | choice | questionid | correct
---+--------+------------+--------
 1 | choice1|          1 |   false
 2 | choice2|          1 |   false
 3 | choice3|          1 |   false
 4 | choice4|          1 |   false
 5 | choice5|          1 |    true
 6 | choice6|          1 |   false
 7 | choice7|          1 |   false
 8 | choice8|          1 |   false

表可能会扩展...

现在，我想选择四个错误答案和正确答案，以在问题下方列出. 而且对于每个用户来说，问题的选择应该有所不同.

Now I want to select four wrong answers and the correct answer to list below the question. And for each user sees the question choices should be different.

如何查询表以获取此结果?我不想进行两个查询并将它们绑定到如下数组中:

How can I query the table for this result? I do not want to make two queries and bind them in an array like below:

SELECT   * 
FROM     `choices` 
WHERE    questionid = :qid AND correct = true

SELECT   * 
FROM `choices` 
WHERE questionid = :qid AND 
      correct = false AND 
      id IN( 
             SELECT  id 
             FROM    `choices` 
             ORDER BY RAND() 
             LIMIT 4
            )

相反，我认为应该一步一步完成.

Instead it should be done with one step I guess.

推荐答案

SELECT *
FROM `choises`
WHERE questionid = :qid
ORDER BY correct DESC, RAND()
LIMIT 5

假设correct是某种int.否则，您可能需要将DESC更改为ASC.

Assuming correct is some sort of int. Otherwise you might need to change DESC to ASC.

您可以像这样使用另外一个ORDER BY RAND()洗牌"这5个结果:

You can 'shuffle' the 5 results using one more ORDER BY RAND() like this:

SELECT * FROM (
    SELECT *
    FROM `choises`
    WHERE questionid = :qid
    ORDER BY correct DESC, RAND()
    LIMIT 5
) as t
ORDER BY RAND()

这篇关于MySQL选择一些随机的行，再加上一个特定的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！