by group mysql 累加_mysql 累加排序求名次

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

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

mysql> select * fromshake_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;

修改成hql语句，在java中如下:

public CreditRank queryRank(int year, int month, intday) {

String hql= "select mobile, sum(credit) as sumCredit, time from CreditLog"

+ " where year(time) = ? and month(time) = ? and day(time) = ?"

+ " group by mobile order by sumCredit desc";

Query query=currentSession().createQuery(hql);

query.setFirstResult(0);

query.setMaxResults(1);

query.setParameter(0, year);

query.setParameter(1, month);

query.setParameter(2, day);

List objsList =query.list();

Object[] firstObjs= objsList.get(0);return new CreditRank(1, (String)firstObjs[0], (Double)firstObjs[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';

这一部分没有转成hql， 因为hibernate不支持from子查询。

那就使用hibernate直接调用sql语句。

public CreditRank queryRank(String mobile, int year, int month, intday) {

String sql= "select rank, mobile, sumCredit " +

"from (select @counter\\:=@counter+1 as rank, mobile, sumCredit " +

"from (select mobile, sum(credit) as sumCredit from shake_log " +

" where year(time) = :year and month(time) = :month and day(time) = :day " +

"group by mobile order by sumCredit desc) as sumList, (select @counter\\:=0) as t) as rankList " +

"where mobile = :mobile";

SQLQuery query=currentSession().createSQLQuery(sql)

.addScalar("rank", StandardBasicTypes.INTEGER)

.addScalar("mobile", StandardBasicTypes.STRING)

.addScalar("sumCredit", StandardBasicTypes.DOUBLE);//设置参数

query.setParameter("year", year);

query.setParameter("month", month);

query.setParameter("day", day);

query.setParameter("mobile", mobile);//查询

List objsList =query.list();if (objsList.size() == 0) {return null;

}

Object[] firstObjs= objsList.get(0);//封装

CreditRank creditRank = new CreditRank((Integer)firstObjs[0], (String)firstObjs[1], (Double)firstObjs[2]);returncreditRank;

}

需要注意:

:= 要 \\:= 才能被识别