自己做的一个小项目里需要用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;
}
需要注意:
:= 要 \\:= 才能被识别