如何对整个表的多列进行排名? [英] How to RANK multiple columns of an entire table?

问题描述

我有一个 SQLite 表，格式如下:

I have an SQLite table which is in the following format:

student physics chemistry maths history english 

学生列包含学生姓名，其他列包含描述各个科目分数的数值.我想为每个学生找出他们学科的排名.例如，对于行，

The student column has student name and the other columns have numeric values depicting score for the individual subjects. What I want to find out for each student is the rank of their subjects. For example, for the row,

Brian 78 62 100 40 50

我希望输出是

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

这是每个学生的所有 5 个科目的排名.输出不需要采用我展示的格式.我只需要一个输出，表明每个学生的每个科目的排名.如何在 SQLite 中实现它?

which is the ranks of all the 5 subjects for each individual student. The output need not be in the format I showed. I just need an output that indicates the rank of each subject for each student. How can I achieve it in SQLite?

我发现了 RANK 和 ROW_NUMBER，但不明白如何将它们用于多列.

I found out RANK and ROW_NUMBER but didn't understand how to use them for multiple columns.

推荐答案

我的逻辑

• 将列转成行并通过union all获取score列
• rank() over partition by student 得到每个学生所有5个科目的排名

架构 (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   |

<小时>

查看 DB Fiddle

这篇关于如何对整个表的多列进行排名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！