MySQL复杂的SQL [英] mysql complicated 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

上面的方法很好，可以返回5个随机问题以及相应的答案.问题在于每个问题有9个答案，其中一个答案是正确的(正确= 1)，而其他答案是错误的(正确= 0).我不想要所有答案，而是想要正确的一个和另外三个随机答案.

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.

任何帮助将不胜感激.

谢谢

史蒂夫

PS:也许最好通过php处理它，但是我不确定.关于此的任何想法也将有所帮助.

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

推荐答案

这是每个Y列TOP X记录的另一个示例.对于每个问题，您都需要4个答案.实际需要两次限制.首先是限制符合条件的问题，然后对另一个排名"答案进行保证，以确保每个问题结果集始终包含正确"的答案.

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.

因此，我的方法是首先对问题应用随机数，以将其作为子集结果，然后将其与答案结合并限制每个Y的X.然后，我们可以将其全部打包.这里的关键是内部查询必须按问题ID排序...而且正确"答案的限定词始终排在第一位，但是之后的任何内容随机分配，总共包括4条记录.

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.

然后，最终查询将WHERE子句应用于仅包含排名顺序为< = 4(对于一个问题可能包含的所有9个答案中的第4个)，然后应用最终的"ORDER BY"子句来保留问题在一起，但是随机化答案，因此"Correct"不再总是返回第一个位置.您可以删除此外部的"ORDER BY"子句以进行测试，只是为了确认功能，然后稍后再添加.

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()

耦合较小的更改...确保每个作业的SQLVars都有:=.最初发布时，我留下了一个:"，可能抛出了错误错误.我还通过使用"a.correct = 1"(没有别名引用)来限定了内部的排序依据".最后，将外部WHERE子句更改为< 5而不是<= 4.我已经完成了每个Y最大的X分组中的许多任务，并且知道它们可以正常工作，只是确定了一些简单的内容.

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.

此外，将IF()随机值调整为第一个值作为十进制数，否则所有随机数均设置为1(整数)，并且永不小数...另外，对于应用ORDERING时可能出现的问题，对所有Q和A进行预查询，以将所有正确答案排在第一位，然后针对该集合应用SQLVars，然后确定排名顺序和排序.

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.

这篇关于MySQL复杂的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！