mysql 累加排序求名次

標籤：des   style   blog   color   sp   on   資料   div   2014   

自己做的一個小項目裡需要用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‘;

 

mysql 累加排序求名次