过程详解
总所周知,Mysql具有联表查询功能,也有随机功能,现在我们需要完成一个问卷发布功能,问卷中的试题全部都是随机生成的,而这些问题所关联的答案不能随机生成(那不就乱套了嘛),这里就需要用到联表查询功能
Mysql随机
首先,先简单介绍一下Mysql的随机,order by Rand()
SELECT * FROM questions ORDER BY RAND() LIMIT 4
示例如上,这段语句翻译过来就是在questions表中随机抽取4个记录(行) LIMIT就是用来约束它的数量
联表查询
目前来说有多种方式联表查询,我这里不做过多介绍,仅举个别简单的例子
SELECT questions.*,questions_answers.* FROM questions,questions_answers
上述语句查询questions表和question_answers表的全部内容,但二者之间没有关联
SELECT questions.*,questions_answers.* FROM questions,questions_answers
WHERE questions.id = questions_answers.question_id
加上一个条件,就可以使问题和答案关联起来
SELECT questions.*,questions_answers.* FROM questions
JOIN questions_answers
ON questions.id = questions_answers.question_id
又或者使用JOIN的方式
子查询
同样的,Mysql也有子查询的功能
SELECT q.*,qa.*
FROM (
SELECT * FROM questions ORDER BY RAND() LIMIT 4
) as q,
questions_answers as qa
代码示例如上,但是这个例子并不妥当,因为它是输出问题表和答案表的全部内容
SELECT q.*,qa.*
FROM (
SELECT * FROM questions ORDER BY RAND() LIMIT 4
) as q,
questions_answers as qa
WHERE q.id = qa.question_id
此时我们加上限制条件where就正常了
SELECT q.*,qa.*
FROM (
SELECT * FROM questions WHERE question_type=0 ORDER BY RAND() LIMIT 5
) as q
JOIN questions_answers as qa ON q.id = qa.question_id
或者使用join的方式
最后提醒:不要忘记了别名,没有别名会报错