单表加入序列号
<!--第一种方法-->
select (@i:=@i+1) as i,table_name.* from table_name,(select @i:=0) as it
<!--第二种方法:-->
set @rownum=0;
select @rownum:=@rownum+1 as rownum, t.username from auth_user t limit 1,5;
多表加入序列号
其实就是把join后的表当成一张表
SELECT @rownum := @rownum +1 as idd ,temp.* from (SELECT `tb1`.* ,(select @rownum:=0)FROM `tb1`
join `tb2` ON tb1.id=tb2.douban
left join `tb3` ON tb1.id=tb3.movie_id
WHERE (查询条件)
ORDER BY `tb3`.`sequence` DESC, `tb3`.`created_at` DESC, `tb2`.`create_at` DESC, `tb1`.`release_timestamp` DESC) as temp