简单记录一下给mysql查询的结果集添加序列号

1.先上一张模拟数据表

2.根据字段LIVE_RATE倒序排列,结果集加上序号

select (@i:=@i+1) as rank,a.* from (select DATE_FORMAT(l.CREATE_DT , '%Y-%m-%d')CREATE_DT,l.DAY_AGE,l.FARMER_NAME,l.LIVE_RATE,l.AREA FROM liverate l WHERE
DATE_FORMAT(l.CREATE_DT , '%Y-%m-%d') >=DATE_FORMAT('2017-06-01','%Y-%m-%d')
and DATE_FORMAT(l.CREATE_DT , '%Y-%m-%d')<=DATE_FORMAT('2017-06-10','%Y-%m-%d')
ORDER BY l.LIVE_RATE desc
LIMIT 0,20)a,(select @i:=0) as it
;