如何按照唯一且预定的顺序(而不是asc或desc)对来自mysql数据库的数据进行排序 [英] How do I sort data from a mysql db according to a unique and predetermined order, NOT asc or desc

问题描述

我需要向学生展示1000个测试问题，每页10个.

I need to show 1000 test questions to a student, 10 per page.

问题在mysql表中，答案将在另一个表中.

The questions are in a mysql table, the answers will go in another table.

我需要每个学生的问题以与其他任何学生不同的预定顺序出现.当他们注册并放置在usermeta表中时，排序顺序是预先确定的.

I need each students questions to appear in a different predetermined order than any other students. The sort order is predetermined when they register and placed in a usermeta table.

在usermeta表中，有一列列出了问题显示的顺序.该列中的顺序对于每个学生而言都是唯一的，并且类似于以下示例:8 | 14 | 97 | 54 | 21 | 37 | 54 | 61 ... etc.

In the usermeta table there is a column that lists the order in which the questions should be shown. The order in that column is unique to each student and looks like this example: 8|14|97|54|21|37|54|61 ...etc.

要向学生显示的第一个问题是第8个问题，然后是第14个问题，然后是第97个问题，依此类推，每页列出10个问题.

The first question to be shown to the student would be question #8, and then question #14, and then question #97, and so on, listing 10 per page.

我不需要按升序或降序对问题进行排序.另外，如果可以帮助找到解决方案，我可以更改db结构.

I don't need to sort the questions asc or desc. Also, I can change the db structure if that would help find a solution.

推荐答案

此外，如果可以帮助找到一个 解决方案.

Also, I can change the db structure if that would help find a solution.

如果可以更改db结构，则不必将排序顺序存储为用管道分隔的字符串，而应将其存储在一个单独的表中，该表将每个问题映射到给定学生应出现的顺序.即

If changing the db structure is possible, then instead of storing the sorting order as a pipe separated string, store it in a separate table that maps each question to the order it should appear in for a given student. i.e.

student_id, sort_order, question_id
1               1               8
1               2               2
1               3               97

然后在为特定学生选择问题时加入排序表.

Then join on your sorting table when selecting your questions for a particular student.

SELECT q.* FROM 
questions q
JOIN questions_sorting_order qso
ON q.id = qso.question_id
ORDER BY qso.sort_order
WHERE qso.student_id = :student_id

这篇关于如何按照唯一且预定的顺序(而不是asc或desc)对来自mysql数据库的数据进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！