MySQL 查询在列值上具有不同的值

Question

我有这个表(有 100k+ 行):

room_id | emote_id | count | since
----------------------------------------
      1 |       22 |    718| 1577135778
      1 |       23 |    124| 1577135178
      1 |       24 |    842| 1577135641
      2 |       22 |    124| 1577135748
      2 |       23 |    345| 1577136441
      2 |       24 |     43| 1577543578
      3 |       22 |     94| 1572135778
      3 |       23 |   4718| 1577135641
      3 |       24 |     18| 1577134661
      4 |       22 |     78| 1577125641
      4 |       23 |    128| 1577135778
      4 |       24 |    278| 1577132577

我想为每个 emote_id 获取该 emote_id 中计数最高的行

因此，对于这个示例，我希望得到以下响应:

room_id | emote_id | count | since
----------------------------------------
      1 |       22 |    718| 1577135778
      3 |       23 |   4718| 1577135641
      1 |       24 |    842| 1577135641

我一直在构建查询，需要帮助。 :(

Answer 1

您可以将嵌套子查询与max()聚合一起使用

select t1.*
  from tab t1
  join (select emote_id, max(count) as count
          from tab
         group by emote_id ) t2
    on t1.emote_id = t2.emote_id
   and t1.count = t2.count

对于 db 版本 8+，您可以使用窗口分析函数，例如 dense_rank():

select room_id, emote_id, count, since
  from
  (
   select t.*, dense_rank() over (partition by emote_id order by count desc) as dr
     from tab t
  ) tt
  where tt.dr = 1  

Demo

count 的所有匹配最大值均通过使用 dense_rank() 返回，以防 tie( 具有相等值 count )任何 emote_id )。如果分析函数为 row_number()，那么即使出现平局，也只会返回一行。