首页 > 代码库 > mysql 累加排序求名次
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‘;
mysql 累加排序求名次
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。