如何在mysql中按升序和降序修改一列

Question

From The Following Mysql Query

select (
   case
   when grade < 8 then null 
   else name
   end
   ) as name,
   grade, marks 
from students
join Grades
ON Min_mark = (SELECT MAX(Min_mark) from grades where min_mark <= students.marks)
order by grade desc,name asc

我想根据grade的值排序，即

生成包含三列的报告:Name, Grade和Mark。报告必须按等级降序排列，即较高的等级先输入。如果有一个以上的学生被分配到同一个年级(1-10)，按他们的名字字母顺序排列这些学生。最后，如果分数低于8，则使用"NULL"作为他们的名字，并按升序

列出他们的分数

select (
   case
   when grade < 8 then null 
   else name
   end
   ) as name,
   grade, marks 
from students
join Grades
ON Min_mark = (SELECT MAX(Min_mark) from grades where min_mark <= students.marks)
 case
    when grade <8 then 
        order by grade desc, name asc
    else 
        order by marks asc
 end

但是由于语法错误，第二个查询没有执行。有人可以建议我如何得到所需的结果。

Answer 1

我的猜测是这样的:

ORDER BY grade > 10 DESC /* orders by grade>10 records first */
, CASE WHEN grade > 10 THEN grade ELSE null END DESC /* all grade<=10 records will "sort" equally among themselves */
, CASE WHEN grade > 10 THEN name ELSE marks END ASC /* all grade<=10 records will "sort" by `name`, others will sort my `marks`  */

Answer 2

我不知道你的列名，但对我来说，这应该是这么简单:

select IF(grade < 8, null, grade) as processed_grade, name 
from students
join Grades
ON students.id = Grades.id_student
order by processed_grade desc,name asc

您只需将grade更改为null，如果低于8，然后按新创建的列和名称排序记录

Answer 3

按两个不同的order by子句对查询进行排序是不可能的。

您可以向语句中添加一个或多个附加行，并按这些行排序。要做到这一点，你必须决定如何排序。10分以下的成绩应该排在所有10分以上的成绩之前吗?

可以这样做:

select (
   case
   when grade < 8 then null 
   else name
   end
   ) as name,
   grade, marks,
   (
   case
   when grade > 10 then grade
   else null
   end
   ) as sortorder1,
   (
   case
   when grade > 10 then name
   else marks
   end
   ) as sortorder2 
from students
join Grades
ON Min_mark = (SELECT MAX(Min_mark) from grades where min_mark <= students.marks)
order by sortorder1 desc, sortorder2 asc

编辑假设成绩低于8分也应按成绩排序:

我认为这应该可以工作:

select (
   case
   when grade < 8 then null 
   else name
   end
   ) as displayname,
   grade, marks 
from students
join Grades
ON Min_mark = (SELECT MAX(Min_mark) from grades where min_mark <= students.marks)
order by grade desc, displayname asc, marks asc

我用'as displayname'代替了'as name'，以便更清楚，排序应该由displayname完成，我不确定这是否需要数据库，但对于阅读查询的人来说更清楚。