获取特定行组中的最大值 [英] Get the max value in a specific group of rows

问题描述

我有这两个表：

流行歌曲

song_name  | rate | country_id
------------------------------
Tic Tac    | 10   | 1
Titanic    | 2    | 1
Love Boat  | 8    | 2
Battery    | 9    | 2

国家

conutry_id | country
--------------------------
1          | United States
2          | Germany

我想要实现的目标是在每个国家/地区获得最受欢迎的歌曲，例如：

What I'd like to achieve is to get the most poular song in each country, e.g.:

song_name | rate | country
--------------------------
Tic Tac   | 10   | United States
Battery   | 9    | Germany

我已经尝试过以下查询：

I've tried this query:

SELECT MAX(rate), song_name, country
FROM popular_song ps JOIN country cnt
ON ps.country_id = cnt.country_id
GROUP BY country

但这不起作用。我尝试查看诸如在分组之前先排序之类的问题，但找不到答案。

But this doesn't work. I've tried looking at questions like "Order by before group by" but didn't find an answer.

哪个mysql查询可以达到此结果？ / strong>

Which mysql query could achieve this result?

推荐答案

有一个技巧可以与 substring_index（）和 group_concat（）：

SELECT MAX(rate),
      substring_index(group_concat(song_name order by rate desc separator '|'), '|', 1) as song,
      country
FROM popular_song ps JOIN
     country cnt
     ON ps.country_id = cnt.country_id
GROUP BY country;

编辑：

如果您有很多每个国家的桌子和很多歌曲，我建议不存在方法：

If you have big tables and lots of songs per country, I would suggest the not exists approach:

select rate, song country
from popular_song ps join
     country cnt
     on ps.country_id = cnt.country_id
where not exists (select 1
                  from popular_song ps2
                  where ps2.country_id = ps.country_id and ps2.rate > ps.rate
                 );

同时带有 popular_song（country_id，费率）上的索引。我建议使用 group_concat（）方法，因为OP已使用 group by 进行了查询，所以诀窍是最容易插入这样的查询。

Along with an index on popular_song(country_id, rate). I recommended the group_concat() approach because the OP already had a query with a group by, so the trick is the easiest to plug into such a query.

这篇关于获取特定行组中的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！