java mysql 排行榜

    /**
     * 排行榜前50条
     */
    public List<User> rank() {

        String sql = "select rank,id,bonus_point,nickname,avatar,state from(" +
                    "select tmp.id,tmp.bonus_point,tmp.nickname,tmp.avatar,tmp.state,@rank:=@rank+1 AS rank from(" +
                    "select id,bonus_point,nickname,avatar,state from u_user order by bonus_point DESC" +
                    ") tmp,(SELECT @rank:=0)a" +
                    ")result where state=0 order by rank asc limit 50;";

        List<User> list = User.dao.find(sql);

        return list;

    }

1.可用mysql中的rank

2.利用临时表

3.所需要的字段都要加上

4.最外层有where条件过滤时，要重新排序，否则会打乱顺序