mysql - 每组选择一个特定的行？

Question

我有一个名为 ratings 的表，看起来像这样

Rating   |   Picid    |   User   8           12          6   7           12          6   9           15          7   5           16          7   9           17          8   10          2           8   7           18          3   10          22          12

我想根据 picid 对评分进行分组，但只选择每个用户评分最高的 picid。我已经有了这样的查询:

SELECT *, AVG(rating) AS total FROM ratings
  GROUP BY picid ORDER by total DESC

此时查询将输出如下信息:

Rating   |   Picid    |   User 7.5           12          6   9           15          7   5           16          7   9           17          8   10          2           8   7           18          3   10          22          12

所以它成功地根据 picid 对评级进行了分组，但我只想要每个用户最高的一个。我希望它输出这样的信息:

Rating   |   Picid    |   User 7.5           12          6   9           15          7   10          2           8   7           18          3   10          22          12

看看它如何只允许每个用户评价最高的 picid？如何修改我的查询以执行此操作？

如果您需要进一步说明我的问题，请告诉我。

Answer 1

您首先必须计算每个 (Picid,User) 组合的平均评分，然后找到 group-wise maximum在那个具体化的结果上:

SELECT Rating, PicID, User
FROM (
  SELECT   AVG(Rating) AS Rating, Picid, User
  FROM     ratings
  GROUP BY Picid, User
) t1 NATURAL JOIN (
  SELECT   MAX(Rating) AS Rating, User
  FROM     (
    SELECT   AVG(Rating) AS Rating, User
    FROM     ratings
    GROUP BY Picid, User
  ) t
  GROUP BY User
) t2

查看 sqlfiddle .