mysql怎么写复杂sql_MySQL复杂的SQL

I asked a question earlier today and received a good response that works well. There was a second part to the question that never got a response so I'm trying again.

SELECT q.id,q.question,a.question_id,a.answer, a.correct

FROM (SELECT * FROM questions q WHERE q.subject_id = 18

ORDER BY RAND() LIMIT 5) q

JOIN answers a on q.id = a.question_id GROUP BY q.id, a.id

The above is fine and returns 5 random questions along with corresponding answers. The problem is that each question has 9 answers, 1 of which is correct (correct = 1) and the others are false (correct = 0). I don't want all the answers but rather the correct one and 3 other random ones.

I've been playing with this for hours and am getting nowhere.

Any help would be appreciated.

Thanks

Steve

PS: Maybe it's better to handle it via php but again I'm not sure. Any thoughts on that would be helpful too.

解决方案

This is another example of a TOP X records per Y example. For every question, you want 4 answers. A LIMIT is actually needed TWICE... First to limit the qualifying questions, and another "ranking" of answers which guarantees the "Correct" answer to ALWAYS be included per question result set.

So my approach is to apply the random against questions first to get that as a subset result, then join that to the answers and limit X per Y. THEN, we can get it all wrapped up. The critical thing here is the inner query has to be ordered by the question ID... AND the qualifier the "Correct" answer is always in first position, but anything after is randomized to include a total of 4 records.

Then, the final query applies the WHERE clause to only include where the ranking sequence is <= 4 (of the possible all 9 answers included for 1 question, but then applies a final "ORDER BY" clause to keep the questions together, but randomizes the answers so the "Correct" is no longer always returned in the first position. You can remove this outer "ORDER BY" clause for testing purposes just to confirm functionality, then add it back in later.

select

FinalQA.*

from

( select

QWithAllAnswers.*,

@RankSeq := if( @LastQuestion = QWithAllAnswers.id, @RankSeq +1, 1 ) ARankSeq,

@LastQuestion := QWithAllAnswers.id as ignoreIt

from

( SELECT

q.id,

q.question,

q.RandQuestionResult,

a.question_id,

a.answer,

a.correct

FROM

( SELECT q.ID,

q.Question,

q.question_ID,

RAND() as RandQuestionResult

FROM

questions q

WHERE

q.subject_id = 18

ORDER BY RAND()

LIMIT 5) JustQ

JOIN answers a

on q.id = a.question_id

ORDER BY

JustQ.RandQuestionResult,

if( a.correct = 1,0.000000, RAND()

) QWithAllAnswers,

( select @RankSeq := 0, @LastQuestion := 0 ) SQLVars

) FinalQA

where

FinalQA.ARankSeq < 5

order by

FinalQA.RandQuestionResult,

rand()

Couple small changes... Make sure at the SQLVars has := for each of the assignments. When I originally posted, I left one ":" off which could have thrown a false error. I also qualified the inner "Order by" by using "a.correct = 1" (had no alias reference). Finally, changed the outer WHERE clause to just < 5 instead of <= 4. I've done MANY of these greatest X per Y groupings and know they work, just missing something simple I'm sure.

Also, adjusted the IF() random to have first value as a decimal, otherwise all randoms get set to 1 (whole number) and never fraction... Also for possible issues of when the ORDERING is applied, I've pre-queried all Q and A pre-sorted to get all Correct answers in the first position, THEN apply the SQLVars against that set, then finalize the rank sequence and ordering.