使用SQL /Rank/稠密_Rank()对表中的字母进行排序

Question

我有不同的等级，并根据他们的分数对他们进行了相应的排序：

select id,subject,semester,student,course,mark,grade
,dense_rank() over(PARTITION by course order by mark desc) as RANK 
from courses c 
LEFT JOIN course_enrolments b ON c.id=b.course;

结果是：

id  subject semester    student course  mark    grade   rank
5270    1001    126    1077055  5270    99        HD    1
5270    1001    126    1076079  5270    98        HD    2
5270    1001    126    1077334  5270    85        HD    3
5270    1001    126    1076920  5270    84        DN    4
5270    1001    126    1077443  5270    84        DN    5
5270    1001    126    1072135  5270    67        CR    6
5270    1001    126    1079997  5270    66        CR    7
5270    1001    126    1071034  5270    62        PS    8
5270    1001    126    1079993  5270    62        PS    9
5270    1001    126    1074070  5270    44        FL    10
5270    1001    126    1078409  5270    30        FL    11
5270    1001    126    1073006  5270    28        FL    12

然而，我想要做到的是按照他们的等级而不是他们的评分来对这些人进行排名

所以我想要实现的是：

id  subject semester    student course  mark    grade   rank
5270    1001    126   1077055   5270    99        HD    1
5270    1001    126   1076079   5270    98        HD    1
5270    1001    126   1077334   5270    85        HD    1
5270    1001    126   1076920   5270    84        DN    2
5270    1001    126   1077443   5270    84        DN    2
5270    1001    126   1072135   5270    67        CR    3
5270    1001    126   1079997   5270    66        CR    3
5270    1001    126   1071034   5270    62        PS    4
5270    1001    126   1079993   5270    62        PS    4
5270    1001    126   1074070   5270    44        FL    5
5270    1001    126   1078409   5270    30        FL    5
5270    1001    126   1073006   5270    28        FL    5

我怎样才能做到这一点？谢谢,

Answer 1

您应该能够完成在oracle和sql server中工作的操作：

select id,subject,semester,student,course,mark,grade,
case when grade = 'HD' then 1
     when grade = 'DN' then 2
     when grade = 'CR' then 3
     when grade = 'PS' then 4
     when grade = 'FL' then 5
end as RANK 
from courses c 
order by mark desc

Answer 2

如果有帮助，我将在Server中使用以下内容。也许你(或某人)能翻译它？

DECLARE @GradeRankTVP Table (
  Grade Varchar(2),
  GradeRank int
);
INSERT INTO @GradeRankTVP (Grade, GradeRank) VALUES
  (HD, 1)
  ,(DN, 2)
  ,(CR, 3)
  ,(PS, 4)
  ,(FL, 5)
-- ,etc
;
select c.id, c.subject, c.semester, b.student, c.course, b.mark, b.grade
, d.GradeRank
from courses c 
LEFT JOIN course_enrolments b ON c.id = b.course
LEFT JOIN @GradeRankTVP AS d on b.grade = d.Grade