如何对整个表的多列进行排序？

Question

我有一个SQLite表，其格式如下：

student physics chemistry maths history english

学生列中有学生姓名，其他列中有描述各个科目的分数的数值。我想为每个学生找到的是他们学科的排名。例如，对于该行，

Brian 78 62 100 40 50

我想要的输出是

Physics - 2
Chemistry - 3 
Maths - 1
History - 5
English - 4

这是每个学生的全部5门科目的排名。输出不必采用我显示的格式。我只需要一个输出，指示每个学生每个学科的排名。如何在SQLite中实现它？

我发现了RANK和ROW_NUMBER，但不了解如何将它们用于多列。

Answer 1

我的逻辑

• 将列转置为行并通过全部合并获得得分列
• 按学生划分的rank（）以获得 the ranks of all the 5 subjects for each individual student
  
  

模式（SQLite v3.26）

CREATE TABLE Table1
    ("student" TEXT(5), "physics" INTEGER, "chemistry" INTEGER, "maths" INTEGER, "history" INTEGER, "english" INTEGER)
;

INSERT INTO Table1
    ("student", "physics", "chemistry", "maths", "history", "english")
VALUES
    ('Brian', 78, 62, 100, 40, 50),
    ('Henry', 55, 72, 85, 22, 50)
;

询问

with cte as (
  select student,'physics' as class,physics as score from Table1 union all
  select student,'chemistry' as class,chemistry as score  from Table1 union all
  select student,'maths' as class,maths as score  from Table1 union all
  select student,'history' as class,history as score  from Table1 union all
  select student,'english' as class,english as score  from Table1 
)
select  student,class,score,RANK() OVER (partition by student order by score desc) rnk
from cte;

| student | class     | score | rnk |
| ------- | --------- | ----- | --- |
| Brian   | maths     | 100   | 1   |
| Brian   | physics   | 78    | 2   |
| Brian   | chemistry | 62    | 3   |
| Brian   | english   | 50    | 4   |
| Brian   | history   | 40    | 5   |
| Henry   | maths     | 85    | 1   |
| Henry   | chemistry | 72    | 2   |
| Henry   | physics   | 55    | 3   |
| Henry   | english   | 50    | 4   |
| Henry   | history   | 22    | 5   |

在数据库小提琴上查看