mysql - 为mysql生成特定的随机数

Question

嗨，我正在为给定的问题做一个程序，但是这个问题对于不同的用户来说需要不同，现在我需要在 mysql 中随机生成问题，尝试使用 RAND() 但我无法使其满足我的要求，这不起作用:

SELECT * FROM oscuro.answers
INNER JOIN questions ON answers.question_id = questions.id
INNER JOIN sub_categories ON questions.sub_category_id = sub_categories.id
WHERE sub_categories.category_id = 1 AND questions.id > RAND();

我需要questions.id表问题上所有记录的随机数，这应该给出一个随机问题以及该问题的所有答案。如何仅为该字段生成随机数？

Answer 1

您可以对子查询进行联接。一个简单的例子:

> SELECT * FROM `foo`;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
> SELECT * FROM foo 
>   INNER JOIN 
>   (SELECT RAND() as random_number)
>     as sqy;

+----+---------------------+
| id | random_number       |
+----+---------------------+
|  1 | 0.16365532499147142 |
|  2 | 0.16365532499147142 |
|  3 | 0.16365532499147142 |
|  4 | 0.16365532499147142 |
|  5 | 0.16365532499147142 |
+----+---------------------+

那么对于你的情况

> SELECT * FROM oscuro.answers
>   INNER JOIN questions
>     ON answers.question_id = questions.id
>   INNER JOIN sub_categories
>     ON questions.sub_category_id = sub_categories.id
>   INNER JOIN (SELECT RAND() as random_number) as sqy
> WHERE
>   sub_categories.category_id = 1
> AND
>   questions.id > random_number;