mysql 累加排序求名次

自己做的一个小项目里需要用mysql计算一些信息。

mysql中的 表如下(注:表中数据都是测试数据,随机生成的)

 

mysql> select * from shake_log;
+-----+-------------+--------+---------------------+------+
| id  | mobile      | credit | time                | type |
+-----+-------------+--------+---------------------+------+
|   1 | 15963097349 |    1.3 | 2014-12-06 21:00:02 |    2 |
|   2 | 18353367706 |    0.3 | 2014-12-06 21:00:05 |    2 |
|   3 | 18369902586 |    0.3 | 2014-12-06 21:00:08 |    2 |
|   4 | 18369905573 |    0.2 | 2014-12-06 21:00:10 |    2 |
|   5 | 18369905170 |    0.5 | 2014-12-06 21:00:13 |    2 |
。。。。。。
。。。。。。
| 125 | 18369905226 |    0.5 | 2014-12-07 05:00:00 |    1 |
| 126 | 18369905226 |    0.2 | 2014-12-07 05:00:02 |    0 |
| 127 | 18369905226 |    0.2 | 2014-12-07 05:00:04 |    0 |
| 128 | 18369905226 |    0.3 | 2014-12-07 05:00:08 |    0 |
。。。。。。
。。。。。。

这张表的含义是这样的,每一条记录代表一次获益,而每个mobile的获益就是当天所有的记录的累加和。

例如上面的记录18369905226有三条记录,那么他今天的获益就是 0.5+0.2+0.2+0.3 = 1.2

 

1. 求获益最多的mobile和值

select mobile, sum(credit) as sumCredit from shake_log where time between 2014-12-06 00:00:00 and 2014-12-06 23:59:59 group by mobile order by sumCredit desc limit 1;

 

2. 求某一具体的mobile按总获益算得的名次

select rank from (select @counter:=@counter+1 as rank, mobile, sumCredit from (select mobile, sum(credit) as sumCredit from shake_log where time between 2014-12-06 00:00:00 and 2014-12-06 23:59:59 group by mobile order by sumCredit desc) as sumList, (select @counter:=0) as t) as rankList where mobile=18369905136;

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。