时间戳

首页 > 解决方案 > 如何按月获取TopN查询组MYSQL

mysql - 如何按月获取TopN查询组MYSQL

问题描述

有一个像这样的表：

months  contact   COUNT
202007  asdas      45
202007  madhouse    1
202007  RORC YANG   1
202007  RORG        2
202007  ROR         5
202008  SARINA      1
202008  SMB         1

我怎样才能得到每个月的前 4 个查询结果？

预期结果：

months  contact   COUNT
202007  asdas      45
202007  ROR         5
202007  RORG        2
202008  SARINA      1
202008  SMB         1

我正在与mysql5.6

标签： mysql

解决方案

这里有2个选择。第一次使用rank() over()不保证每月只有 4 行（可能更多），第二次使用row_number() over()将限制行数每月最多 4 行

select
    *
from (
      select
          * , rank() over(partition by months order by c desc) as cr
      from (
        select  months, contact, count(*) as c
        from mytable
        group by months, contact
        ) as g
    ) as d
where cr <= 4
;

select
    *
from (
      select
          * , row_number() over(partition by months order by c desc) as rn
      from (
        select  months, contact, count(*) as c
        from mytable
        group by months, contact
        ) as g
    ) as d
where rn <= 4
;

看演示

对于较旧的 MySQL，请尝试行号破解：

select
    *
from (
      select
            @row_num :=IF(@prev_value=g.months,@row_num+1,1)AS RowNumber
          , g.months
          , g.contact
          , g.c
          , @prev_value := g.months
      from (
        select  months, contact, count(*) as c
        from mytable
        group by months, contact
        ) as g
      CROSS JOIN (SELECT @row_num :=1,  @prev_value :='') vars
      ORDER BY g.months, g.contact
    ) as d
where RowNumber <= 4

在演示中看到

推荐阅读