MySQL Greatest N 结果与连接表

Question

选择前 n 个结果，我在这里看到了很多关于如何做到这一点的帖子和精彩文章，但我很难用我的数据集来做到这一点。大多数示例都集中在数据集上，而不需要额外的连接。

我一直在尝试应用 http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ 中的示例对我的查询没有多大成功。

存在Person、Credit 和Media 三个表。

Person 链接到 Credit 和 Credit to Media。

下面的查询应该返回每个人的前 5 个媒体，但它没有，我哪里错了？

SELECT 
p.id AS person_id, 
c.id AS credit_id, 
m.id AS media_id, m.rating_average
FROM person p
INNER JOIN credit c ON c.person_id = p.id
INNER JOIN media m ON m.id = c.media_id
where (
   select count(*) from media as m2
    inner JOIN credit c2 on m2.id=c2.media_id
   where c2.person_id = c.person_id and m2.rating_average >= m.rating_average
) <= 5

澄清:

热门媒体是根据 rating_average 最高的媒体计算得出的。

更新:

SQLFiddle http://sqlfiddle.com/#!9/eb0fd

每人 (p) 前 3 个媒体 (m) 的期望输出。显然，我希望能够为排名前 5 的媒体做到这一点，但这只是测试数据。

p   m   c   rating_average
1   9   27  9
1   7   28  8
1   1   1   8
2   1   5   8
2   4   8   8
2   7   29  8
3   4   10  8
3   3   9   6
3   5   11  5
4   3   13  6
4   5   14  5
4   6   15  3
5   4   16  8
5   5   17  5
5   6   18  3
6   6   19  3
7   7   20  8
8   9   23  9
8   1   21  8
8   8   22  0
9   1   24  8
9   7   26  8
9   5   25  5

Answer 1

我想我解决了:)

首先，这是一个基于您开始方式的解决方案。但是有一个问题我无法解决它以显示每个 person_id 的确切 3(或者你选择的任何数字，例如我选择 3)行。问题是解决方案是基于计算 rating_average 大于当前行的行数。因此，如果您有 5 个相同的最高值，您可以选择显示所有 5 个或根本不显示它们，这并不好。所以这就是你这样做的方式......(当然这是一个例子，如果你有 4 个最高值(value)，你会把它们全部显示出来(我认为不显示数据根本没有意义))......

 SELECT t1.person_id, t1.credit_id, t1.media_id, t1.rating_average
 FROM (SELECT p.id AS person_id, c.id AS credit_id, m.id AS media_id, 
              m.rating_average AS rating_average
       FROM person p
       INNER JOIN credit c ON c.person_id = p.id
       INNER JOIN media m ON m.id = c.media_id) as t1
 WHERE (SELECT COUNT(*) 
       FROM (SELECT p.id AS person_id, c.id AS credit_id, m.id AS media_id, 
                    m.rating_average AS rating_average
             FROM person p
             INNER JOIN credit c ON c.person_id = p.id
             INNER JOIN media m ON m.id = c.media_id) AS t2
       WHERE t2.person_id = t1.person_id AND t2.rating_average > t1.rating_average) < 3
 ORDER BY person_id ASC, rating_average DESC

重要:如果您没有 self 重复的值(value)，这个解决方案可以工作(为每个人显示确切的 3 行)......这是 fiddle http://sqlfiddle.com/#!9/eb0fd/64你可以看到 person_id 为 1 的问题!

在那之后我又玩了一点，让它在我认为的问题中按照你想要的方式工作。这是一个代码:

SET @num := 0, @person := 0;

SELECT person_id, credit_id, media_id, rating_average, rowNumber 
FROM (SELECT t1.person_id, t1.credit_id, t1.media_id, t1.rating_average,
             @num := if(@person = t1.person_id, @num + 1, 1) AS rowNumber,
             @person := t1.person_id
      FROM (SELECT p.id AS person_id, c.id AS credit_id, m.id AS media_id, 
                   m.rating_average AS rating_average
            FROM person p
            INNER JOIN credit c ON c.person_id = p.id
            INNER JOIN media m ON m.id = c.media_id
            ORDER BY p.id ASC, m.rating_average DESC) as t1) as t2
 WHERE rowNumber <= 3

这是 http://sqlfiddle.com/#!9/eb0fd/65 的 fiddle ...

GL!

P. S. 对不起我的英语，希望你能理解我在说什么......