sql - 如何根据 SQLite 表中的总分检索排名

Question

假设我有一个这样的表:

 S_id  |ca1 |ca2 |exam
  1    | 08 | 12 | 35
  1    | 02 | 14 | 32
  1    | 08 | 12 | 20
  2    | 03 | 11 | 55
  2    | 09 | 18 | 45
  2    | 10 | 12 | 35
  3    | 07 | 12 | 35
  3    | 04 | 14 | 37
  3    | 09 | 15 | 32
  4    | 03 | 11 | 55
  4    | 09 | 18 | 45
  4    | 10 | 12 | 35
  5    | 10 | 12 | 35
  5    | 07 | 12 | 35
  5    | 09 | 18 | 45

我想选择 S_id、total 并根据 sum(ca1+ca2+exam) 为每个学生分配排名，如下所示:

 S_id |total|rank
   1  | 158 | 5
   2  | 198 | 1
   3  | 165 | 4
   4  | 198 | 1
   5  | 183 | 3

如果有相同的总数，比如 S_id 2 和 S_id 4 排名为 1，我希望排名跳到 3。

感谢您的帮助。

Answer 1

制作表格:

sqlite> create table t (S_id, ca1, ca2, exam);
sqlite> insert into t values 
   ...> ( 1 , 08 , 12 , 35 ),
   ...> ( 1 , 02 , 14 , 32 ),
   ...> ( 1 , 08 , 12 , 20 ),
   ...> ( 2 , 03 , 11 , 55 ),
   ...> ( 2 , 09 , 18 , 45 ),
   ...> ( 2 , 10 , 12 , 35 ),
   ...> ( 3 , 07 , 12 , 35 ),
   ...> ( 3 , 04 , 14 , 37 ),
   ...> ( 3 , 09 , 15 , 32 ),
   ...> ( 4 , 03 , 11 , 55 ),
   ...> ( 4 , 09 , 18 , 45 ),
   ...> ( 4 , 10 , 12 , 35 ),
   ...> ( 5 , 10 , 12 , 35 ),
   ...> ( 5 , 07 , 12 , 35 ),
   ...> ( 5 , 09 , 18 , 45 );

用总分制作一个临时表:

sqlite> create temp table tt
        as select S_id, sum(ca1) + sum(ca2) + sum(exam) as total
        from t group by S_id;

使用临时表计算排名:

sqlite> select s.S_id, s.total,
          (select count(*)+1 from tt as r where r.total > s.total) as rank
          from tt as s;
1|143|5
2|198|1
3|165|4
4|198|1
5|183|3

删除临时表:

sqlite> drop table tt;

附录

随着最近对 SQLite 的更改 (2015-02-09)，此公式现在有效:

with tt (S_id, total) as 
   (select S_id, sum(ca1 + ca2 + exam) as total from t group by S_id)
select s.S_id, s.total,
       (select count(*)+1 from tt as r where r.total > s.total) as rank
   from tt as s;