我想我解决了:)
首先,这是一个基于您开始方式的解决方案。但有一个问题,我无法解决它为每个 person_id 显示精确的 3 行(或者您选择的任何数字,例如我选择 3 行)。问题是,解决方案是基于计算有多少行 rating_average 大于当前行。因此,如果您有 5 个相同的最高值,您可以选择显示全部 5 个或根本不显示它们,但这不好。所以这就是你这样做的方式......(当然,这是一个例子,如果你有 4 个最高值,你就将它们全部显示出来(我认为不显示数据根本没有意义))......
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
重要的:如果您没有自我重复的值,则此解决方案可以工作(为每个人显示精确的 3 行)...这是小提琴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 ...
GL!
P.S.对不起我的英语希望你能明白我在说什么......