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(); }