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;

 

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

 

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

        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<Object[]> 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, int day) {

        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<Object[]> 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]);

        return creditRank;
    }

 

 

需要注意: 

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