Oracle中有一个伪列rownum,可以在生成查询结果表的时候生成一组递增的序列号。MySQL中没有这个伪列,但是有时候要用,可以用如下方法模拟生成一列自增序号。
SELECT
r.id_,(@i :=@i + 1) AS numbers
FROM
test_user r,(SELECT @i:=0) AS it
WHERE
r.is_delete_ = 0
and r.electronic_id_ = #{electronicId}
ORDER BY r.gmt_create_ DESC
上面,根据数据库的某一个字段排序后,增加一列作为每一条的数据的所在行
(SELECT @i:=0) AS it 设置数据所在行的开始 @i:=0表示从1开始
(@i :=@i + 1) AS numbers表示每次递增1
查询某一条数据的行号
select
a.numbers
from
(select
r.id_,(@i :=@i + 1) as numbers
from
test_user r,(select @i:=0) as it
where
r.is_delete_ = 0
and r.electronic_id_ = #{electronicId}
order by r.gmt_create_ desc
) as a
where a.id_ = #{nextId}