mysql获取每个分类排名靠前的数据

乐乐呀�

已于 2022-11-11 15:47:20 修改

阅读量836

点赞数

文章标签： mysql 分类数据库后端

于 2022-02-21 15:16:38 首次发布

本文链接：https://blog.csdn.net/weixin_44349028/article/details/123047949

版权

利用row_number() over()函数，Mysql8以上可用
利用mysql的变量来实现

select t3.songid
from(
select @gid := @cgid, @cgid := t1.SingerId, if(@gid = @cgid, @rank := @rank + 1, @rank := 1) AS rank, t1.*
          from (
              select a.singerId as singerId,a.songid, ifnull(b.count,0)as count
              from sod_song_ksc a left join song_click_count b on a.songId = b.songid
              where a.SingerId in(123,654)
              ORDER BY a.singerId desc,b.count desc,a.songid desc
          ) as t1,
          (select @gid := -1, @cgid := -1, @rank := 1) t2
) as t3
      where t3.rank  <=10

说明：(select @gid := -1, @cgid := -1, @rank := 1)三个变量为了初始化，然后上面赋值并且做判断，if(@gid = @cgid, @rank := @rank + 1, @rank := 1)如果gid==cgid，则rank+1，一行一行的比较，ORDER BY a.singerId desc,b.count desc,a.songid desc为整个结果集的排列顺序。这种方式不会增加多余的大表关联，性能高。