mysql排序行号_mysql order by limit 与行号 冲突问题

场景是这样的，有一张表 存放 作品  spring_outing

一张表 存放对 这个作品的 投票数     spring_vote

要求：统计每个作品的总票数，票数从高->低 排序，统计排名，相同票数，排名相同，

--------------------------------- 第一次修改 (只考虑了 排名)------------------------------

SELECT DISTINCT

(@ranking:=@ranking+1) as ranking,

b.count,t.*

from spring_outing t

LEFT JOIN (

select sum(1)as count , works_no as worksNo from spring_vote where is_deleted = 0 GROUP by worksNo) B on B.worksNo = t.works_no

, (select@ranking:=0) r

WHERE t.is_deleted = 0

order by count desc ,id

一开始是这样写的，发现 ranking (排名)混乱

limit 0,10

--------------------------------  再次 修改 (排名 + 过滤) --------------------------

因为 order by 是在数据查询完之后 执行

而行号 是在查询时，就开始赋值了，

所以需要把 查询的 内容 包成 一张表，所有的 where，order by 都在里面执行

然后再 赋值行号，然后 再  limit，

那么 问题 又来了， limit 没得效果啊啊啊啊，每次翻页之后，ranking 都是从1开始，又gg了

SELECT

@rownum:=@rownum+1 AS rownum,

if(@count=count,@ranking,@ranking:=@rownum) as ranking,

@count:=count,  a.*

from

( select t.id as id,t.works_no as worksNo, t.client_name as clientName, t.img_url as imgUrl,

t.status as status, ifnull(b.count,0) as count

from spring_outing t

LEFT JOIN

( select sum(1)as count , works_no as worksNo from spring_vote where is_deleted = 0

GROUP by worksNo) B on B.worksNo = t.works_no

where  t.is_deleted = 0

order by count desc ,id

)A ,(select@ranking:=0,@rownum:=0,@count:=null) r

limit 0,10

limit 3,2

-------------------------------- 第三次 修改 (完成)------------------------------

SELECT DISTINCT

@rownum:=@rownum+1 AS rownum,

if(@count=count,@ranking,@ranking:=@rownum) as ranking,

@count:=count, a.*

from

( SELECT t.id as id,t.works_no as worksNo, t.client_name as clientName, t.img_url as imgUrl,

t.status as status, ifnull(b.count,0) as count

from spring_outing t

LEFT JOIN (

select sum(1)as count , works_no as worksNo from spring_vote where is_deleted = 0 GROUP by worksNo) B on B.worksNo = t.works_no

WHERE t.is_deleted = 0

order by count desc ,id

)A, (select@ranking:=0,@rownum:=0,@count:=null) r

limit 0,10

limit 3,2

只是做个记录 作为参考，

有很多说不对的地方，请指正，

写的时候还参考了 下面博主的资料，他讲的比较全面 合适

https://blog.csdn.net/a56508820/article/details/49663069