mysql 给返回列表排序添加序号列

SELECT (@i:=@i+1) rankNum,userLeaderboard.userId,userLeaderboard.userName,userLeaderboard.totalAmount
	FROM (
			SELECT 
				s.user_id userId,u.USER_NAME userName,sum(s.pay_amount) totalAmount
				FROM t_sale_deal_info s,t_user u
				WHERE s.user_id=u.id AND s.pay_date_time>='2020-07-03 00:00:00' AND s.pay_date_time<='2020-07-07 23:59:59'
				GROUP BY s.user_id
			) userLeaderboard,
			(select @i:=0) as it
	ORDER BY userLeaderboard.totalAmount desc

:=   赋值的意思。在set update select 中表示赋值的意思，用的比较少一般都用=，但是在用变量实现行号时（比如本文标题获取排列序号），一定要用:=。

 =   等于的意思，只有当set 和 update时，和:=的意思是一样的，表示赋值，其余情况都是等于的意思。

上面的sql主要在(@i:=@i+1) rankNum和 (select @i:=0) as it这两点

 

注意如果是JPA hibernate注意:冒号，框架会识别为参数的前缀，需要\\转义,如:

@Override
    public List<Map<String, Object>> getAllAmountByUser(String startTime, String endTime) {
        String sql=" SELECT (@i\\:=@i+1) rankNum,userLeaderboard.userId,userLeaderboard.userName,userLeaderboard.totalAmount "+
                   "     FROM ( "+
                   "             SELECT "+
                   "             s.user_id userId,u.USER_NAME userName,sum(s.pay_amount) totalAmount "+
                   "             FROM t_sale_deal_info s,t_user u "+
                   "             WHERE s.user_id=u.id AND s.pay_date_time>=:startTime AND s.pay_date_time<=:endTime "+
                   "             GROUP BY s.user_id "+
                   "           ) userLeaderboard, "+
                   "           (select @i\\:=0) as it "+
                   "     ORDER BY userLeaderboard.totalAmount desc";

        Query query = em.createNativeQuery(sql);
        query.setParameter("startTime",startTime);
        query.setParameter("endTime",endTime);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        return query.getResultList();
    }