模板如下:
-
SELECT (@rownum:=@rownum+1) 自增序号别名, 结果集字段
-
FROM 结果集, (SELECT @rownum:=0) AS 任意别名
示例如下:
-
SELECT @rownum:=@rownum+1 AS rownum, A.*
-
FROM (
-
SELECT U.*
-
FROM `users` U
-
ORDER BY U.`id` DESC
-
) A, (SELECT @rownum:=0) B
上述语句的效果等同于下述语句:
-
Set @rownum:=0;
-
Select (@rownum:=@rownum+1) as rownum, 结果集A.* from 结果集A
上述的方法同样适用于分页查询:
-
Select (@rownum:=@rownum+1) as rownum, A.*
-
FROM 结果集 A,(Select @rownum:=0) B
-
order by A.id desc
-
limit 0, 10;
-
Set @rownum:=0;
-
Select (@rownum:=@rownum+1) as rownum, A.*
-
FROM 结果集 A
-
order by A.id desc
-
limit 0, 10;
补充: 如果是在MyBatis中使用上述查询时,变量i的的初始值0,可以使用传参的方式(¥{})进行设置。
参考: