MySql使用FIELD优先排序

Question

学生表:

----------------------
id  | name 
______________________
1   | Name 1
2   | Name 2

类(class)表:

id | student_id | ctype    | level
__________________________________
1  | 2          | beginner | complete
2  | 2          | advanced | current
3  | 1          | beginner | current 
4  | 2          | intermed | skipped 

从上面两个表中，我试图根据类(class)表中的级别获取最新的用户记录。级别应该匹配，以便以相同的顺序检查当前、完整和跳过，因此如果用户具有任何类(class)类型的当前级别，则应该获取它，否则检查级别完成...

我正在使用以下查询。

SELECT `sc`.`student_id`, 
        `s`.`name`,  
        `sc`.`id` as `course_id`, 
        `sc`.`ctype`, 
        `sc`.`level`,
FROM `course` `sc`
LEFT JOIN `students` `s` ON `s`.`id` = `sc`.`student_id`
WHERE sc.id = (SELECT ssc.id FROM course ssc WHERE ssc.student_id = sc.student_id  
ORDER BY FIELD(`ssc`.`level`,"current","complete","skipped") DESC LIMIT 1,1)
GROUP BY `sc`.`student_id`
ORDER BY `sc`.`id` DESC
LIMIT 20

上述查询的问题是仅当类(class)表中有多个用户 ID 匹配时才会显示。所以我得到的最终输出是它仅显示 id 2 的学生并忽略 id 1 的学生，因为不超过一个。

查询结果表格

   student_id | name   |  course_id | ctype    | level |
=====================================================
     2        | Name 2 |  2         | advanced | current

预期结果

   student_id | name   |  course_id | ctype    | level |
=====================================================
     2        | Name 2 |  2         | advanced | current
     1        | Name 1 |  3         | beginner | current

注意:我也尝试过使用FIELD_IN_SET 和IN 而不是FIELD，我得到了相同的结果

Answer 1

将 LIMIT 1,1 更改为 LIMIT 0,1 或仅 LIMIT 1。

与 SQL 中的大多数其他内容不同，LIMIT 子句中的 offset 字段是从 0 开始的，而不是从 1 开始的。因此，如果只有 1 个匹配行，LIMIT 1,1 会跳过它。如果有 2 个或更多匹配行，则您不会获得最上面的匹配项，而是获得第二个匹配项。

此外，排序应为 ASC，而不是 DESC，因为您希望选择最低字段(current)，而不是最高字段.

SELECT `sc`.`student_id`, 
        `s`.`name`,  
        `sc`.`id` as `course_id`, 
        `sc`.`ctype`, 
        `sc`.`level`
FROM `course` `sc`
LEFT JOIN `students` `s` ON `s`.`id` = `sc`.`student_id`
WHERE sc.id = (
    SELECT ssc.id FROM course ssc 
    WHERE ssc.student_id = sc.student_id  
    ORDER BY FIELD(`ssc`.`level`,"current","complete","skipped") ASC 
    LIMIT 0,1)
GROUP BY `sc`.`student_id`
ORDER BY `sc`.`id` DESC
LIMIT 20

DEMO

也不需要 GROUP BYsc.student_id`。该查询仅返回每个学生一个类(class) ID，因此每个学生不能有多行。