MySQL:获取组的最大值 [英] MySQL : Get max values of groups

问题描述

我有一张桌子:

user_id | fav_song_genre | votes_as_fav_member
--------+----------------+--------------------
      1 | hip hop        | 3
      2 | hip hop        | 5
      3 | rock           | 8
      4 | rock           | 6

我如何仅获得每组fav_song_genre中具有最高表决数的user_id的结果:

How do I get only results of user_id's with the highest votes_as_fav_member per group fav_song_genre:

类似

SELECT *, MAX(votes_as_fav_member) as most_votes 
FROM   table 
GROUP BY 
       fav_song_genre

我正在使用它，但没有给我每个流派最多票数的会员ID.

I'm using that but it's not giving me the ID's of the members with most votes per genre.

推荐答案

推理就像

每个流派的
• SELECT最高票数
• JOIN返回原始表，以检索找到的记录的其他列.

• SELECT max vote for each genre
• JOIN back with the original table to retrieve the additional columns for the records found.

SQL语句

SELECT  us.*
FROM    UserSongs us
        INNER JOIN (
          SELECT  fav_song_genre
                  , MAX(votes_as_fav_member) AS votes_as_fav_member
          FROM    UserSongs
          GROUP BY
                  fav_song_genre
        ) usm ON usm.fav_song_genre = us.fav_song_genre
                 AND usm.votes_as_fav_member = us.votes_as_fav_member

修改

我如何确保返回具有较低ID的人

SELECT  MIN(us.user_id) as user_id
        , us.fav_song_genre
        , us.votes_as_fav_member
FROM    UserSongs us
        INNER JOIN (
          SELECT  fav_song_genre
                  , MAX(votes_as_fav_member) AS votes_as_fav_member
          FROM    UserSongs
          GROUP BY
                  fav_song_genre
        ) usm ON usm.fav_song_genre = us.fav_song_genre
                 AND usm.votes_as_fav_member = us.votes_as_fav_member
GROUP BY
        us.fav_song_genre
        , votes_as_fav_member

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