MYSQL 子查询需要返回多行，父查询的每个结果 LIMIT 5

Question

我的 NFL 数据库有三个表

玩家 - 具有玩家 ID/姓名

stats_passing - 按比赛显示球员 ID、game_id、码数和达阵次数

比赛 - 有比赛日期

我正在努力找出过去五场比赛中码数和达阵次数最多的前四名球员。

到目前为止我有这个:

SELECT
    p.name,
    SUM(s.yds) AS tot_yds,
    SUM(s.tds) AS tot_tds
FROM players AS p
INNER JOIN (
    SELECT
        s.id,
        s.player_id,
        s.yds,
        s.tds
    FROM stats_passing AS s
    INNER JOIN games AS g ON s.game_id = g.id
    WHERE YEAR(g.date) = 2014
    ORDER BY s.player_id, g.date DESC
    LIMIT 5) AS s ON p.id = s.player_id
ORDER BY tot_yds DESC, tot_tds DESC
LIMIT 4

但是它只返回一个结果。

Drew Brees, 1497, 10

但正如我上面所说，我希望前四名球员按总码数和总达阵次数排序，类似于

Drew Brees, 1497, 10
Tom Brady, 1234, 9
Andrew Luck, 1123, 8
Aaron Rodgers, 1023, 7

我已经为此工作了几天，我认为我的 mysql 知识已经结束了。请帮忙!提前致谢!

Answer 1

想通了。希望这对其他人有帮助。如果效率低下，请随意添加。

SET @rn = 0;
SET @prev = 0;

SELECT
    name,
    tot_yds,
    tot_tds
FROM (
    SELECT
        p.name,
        SUM(s.yds) AS tot_yds,
        SUM(s.tds) AS tot_tds
    FROM players AS p
    INNER JOIN (
        SELECT
            s.id,
            s.player_id,
            s.yds,
            s.tds,
            @rn := CASE WHEN @prev = s.player_id
                    THEN @rn + 1
                    ELSE 1
                   END AS rn,
            @prev := s.player_id
        FROM (SELECT
                st.id,
                st.player_id,
                st.yds,
                st.tds
             FROM stats_passing AS st
             INNER JOIN games AS g ON st.game_id = g.id
              WHERE YEAR(g.date) = 2014
            ORDER BY st.player_id, g.date DESC) AS s
        ) AS s ON p.id = s.player_id 
     WHERE rn <= 5
     GROUP BY p.name) AS ns
 ORDER BY tot_yds DESC, tot_tds DESC
 LIMIT 4