mysql - MySQL排序表，将一组值的一列之和

我有一个学生考试数据数据库，类似于下面显示的数据库。我试图通过将一个学期（ranks）的所有科目（Marks）的Subj的总和与之相应地排序，来为这些学生分配Sem。

因此，如果这是原始数据库：

    Name | S.NO.| Subj | Sem | Marks | Rank
    ab   |  B1  |  C1  | 1   |  002  |
    ab   |  B1  |  C2  | 1   |  004  |
    ab   |  B1  |  C3  | 1   |  005  |
    ab   |  B1  |  C4  | 2   |  008  |

    xy   |  C2  |  C1  | 1   |  006  |
    xy   |  C2  |  C2  | 1   |  004  |
    xy   |  C2  |  C3  | 2   |  007  |
    xy   |  C2  |  C4  | 2   |  009  |
    xy   |  C2  |  C5  | 2   |  003  |

    sm   |  Z1  |  C1  | 1   |  006  |
    sm   |  Z1  |  C2  | 2   |  004  |
    sm   |  Z1  |  C3  | 2   |  008  |

我想将其转换为：

    Name | S.NO.| Subj | Sem | Marks | Rank
    ab   |  B1  |  C1  | 1   |  002  |   1(coz his total is 11 in sem 1)
    ab   |  B1  |  C2  | 1   |  004  |   1
    ab   |  B1  |  C3  | 1   |  005  |   1

    ab   |  B1  |  C4  | 2   |  008  |   3

    xy   |  C2  |  C1  | 1   |  006  |   2
    xy   |  C2  |  C2  | 1   |  004  |   2

    xy   |  C2  |  C3  | 2   |  007  |   1(coz his total is 19 in Sem 2)
    xy   |  C2  |  C4  | 2   |  009  |   1
    xy   |  C2  |  C5  | 2   |  003  |   1

    sm   |  Z1  |  C1  | 1   |  006  |   3

    sm   |  Z1  |  C2  | 2   |  004  |   2
    sm   |  Z1  |  C3  | 2   |  008  |   2

该数据库大约有30万行。

最佳答案

这就是你想要的

select name,sno,subj,sem,mark,rank
from
(
select  e1.*
       , @sm:=(select sum(e2.mark) from exams e2 where e1.name=e2.name and e1.sem=e2.sem) summark
       , case when @sem!=sem then @r:=1
              when @s  !=@sm then @r:=@r+1
                             else @r
         end as rank
       , @s:=@sm
       , @sem:=sem
       from exams e1,(select @r:=0, @s:=0,@sm:=0,@sem:=0) r
order by sem,summark desc
) r2
order by sno,subj;

我不知道运行时。可能需要一点点。

关于SQL Fiddle的演示：http://sqlfiddle.com/#!9/b0290/1

以上解释一下。 MySQL没有其他数据库（例如Oracle）那样的分析功能。但是上面模拟了它。 @sm是分数的总和。 @r是等级变量。如果分数和改变，则增加。当学期更改时，它将重置为1。 @s保存上一行的总和，@sem保存上一行的学期。