mysql - 按特定编号查询订单，然后按升序进行排序

表主题：

+--------+----+
|  name  | id |
+--------|----+
| apple  | 3  |
| banana | 1  |
| tree   | 4  |
| horse  | 6  |
| bird   | 7  |
| raq    | 2  |
+--------+----+

所以我尝试使用这样的输出（例如，如果我的$id_subject = 4，那么我的下一行必须是5-67-1….）

+--------+----+
|  name  | id |
+--------+----+
| tree   | 4  |
| horse  | 6  |
| bird   | 7  |
| banana | 1  |
| raq    | 2  |
| apple  | 3  |
+--------+----+

我的问题：

select subject_url,subjects.id
    from students_group
    left join teacher_group on teacher_group.group_school_id = students_group.group_id
    left join subjects on teacher_group.subject_id = subjects.id
    where   students_group.user_id = 83
    ORDER BY subjects.id = 5 desc

但我得到了这个结果：

+--------+----+
|  name  | id |
+--------+----+
| tree   | 4  |
| apple  | 3  |
| banana | 1  |
| horse  | 6  |
| bird   | 7  |
| raq    | 2  |
+--------+----+

我怎样才能让它按预期工作？

最佳答案

ORDER BY
if (subjects.id=$id_subject, -1,
  if (subjects.id>$id_subject, 0, 1)
), subjects.id