SELECT q.*
FROM mdl_question q
INNER JOIN mdl_question_categories qc ON
qc.id = q.category
AND q.hidden='0'
AND q.parent='0'
LEFT JOIN mdl_question_attributes qa ON
q.id = qa.q_id
WHERE qtype = 'match'
AND IFNULL( qa.commonmodulus, 0.1 ) >= 0.1
AND IFNULL( qa.commonmodulus, 0.1 ) <= 0.5
AND FIND_IN_SET(qc.contextid,'10, 3, 1')
ORDER BY RAND() ASC limit 1
union all
SELECT q.*
FROM mdl_question q
INNER JOIN mdl_question_categories qc ON
qc.id = q.category AND q.hidden='0'
AND q.parent='0'
LEFT JOIN mdl_question_attributes qa ON
q.id = qa.q_id
WHERE qtype = 'match'
AND IFNULL( qa.commonmodulus, 0.1 ) >= 0.5
AND IFNULL( qa.commonmodulus, 0.1 ) <= 0.9
AND FIND_IN_SET(qc.contextid,'10, 3, 1')
ORDER BY RAND() ASC limit 1上面这段主要是想把两个用了ORDER BY RAND()的union all起来,
不过执行的时候报错,要是想通过的话,该怎么改呢?第一个select和第二个select差不多,都是从抽出的数据里面随机抽取一条数据。
SELECT q.*
FROM mdl_question q
INNER JOIN mdl_question_categories qc ON
qc.id = q.category
AND q.hidden='0'
AND q.parent='0'
LEFT JOIN mdl_question_attributes qa ON
q.id = qa.q_id
WHERE qtype = 'match'
AND IFNULL( qa.commonmodulus, 0.1 ) >= 0.1
AND IFNULL( qa.commonmodulus, 0.1 ) <= 0.5
AND FIND_IN_SET(qc.contextid,'10, 3, 1')
ORDER BY RAND() ASC limit 1 ) a
union all
select * from (
SELECT q.*
FROM mdl_question q
INNER JOIN mdl_question_categories qc ON
qc.id = q.category AND q.hidden='0'
AND q.parent='0'
LEFT JOIN mdl_question_attributes qa ON
q.id = qa.q_id
WHERE qtype = 'match'
AND IFNULL( qa.commonmodulus, 0.1 ) >= 0.5
AND IFNULL( qa.commonmodulus, 0.1 ) <= 0.9
AND FIND_IN_SET(qc.contextid,'10, 3, 1')
ORDER BY RAND() ASC limit 1) b