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