mysql - 从 2 个 Mysql 表中为每个问题选择 4 个答案

Question

我有两个表“问题”和“答案”，我需要选择 4 个随机问题及其 4 个答案

我尝试了这个:(但每个问题仅返回 1 个答案)

SELECT question.id,question.text,answer.id,answer.text
FROM question
LEFT JOIN answer ON answer.question_id = question.id
ORDER BY RAND( ) 
limit 4

表格:

问题

id     text
1     where is...?
2     is .....?
3     How old ...?
4     from where...?
5     where are ...?
6     is England ...?

回答

id   question_id     text
1         1           bla..
2         1           18
3         1           19
4         1           jj
5         2           kk
6         2           82
7         2           77
8         2           77
9         3           6
10        3           2

谢谢

Answer 1

首先使用子查询选择随机问题，然后加入答案表:

SELECT q.id,
    q.text,
    a.id AS a_id -- always use an alias when you have duplicate coloumn names
    a.text a_text
FROM
    (SELECT
         id, text
     FROM
         questions
     ORDER BY RAND() LIMIT 4) q
JOIN
    answers a
    ON a.question_id = q.id

编辑:键入时发布答案