php - secondSELECT语句的总和-PHP-MySQL

我有以下的问题，其中工作。。。但我需要一些改变：

SELECT
    p.pid,
    p.name,
    GROUP_CONCAT( gC.leaguepoints ORDER BY leaguepoints DESC ) AS leaguepoints
FROM
    golf_player p
LEFT JOIN
    golf_card gC ON
    p.pid = gC.pid
GROUP BY
    p.pid
ORDER BY
    p.name
DESC

我真正想要的是返回的另一个属性totaleaguepoints，它是我表中最近20个联赛积分的总和。
如何为群连接添加限制？
可能是这样的？

 SELECT
      p.pid,
      p.name,
      GROUP_CONCAT( gC.leaguepoints ORDER BY leaguepoints DESC ) AS leaguepoints,
      SUM(
        SELECT
          gC2.leaguepoints
        FROM
          golf_card gC2
        WHERE
          gC2.pid = p.pid
        ORDER BY
          leaguepoints
          DESC
        LIMIT 20
      ) AS totalleaguepoints
  FROM
    golf_player p
  LEFT JOIN
    golf_card gC ON
    p.pid = gC.pid
  GROUP BY
    p.pid
  ORDER BY
    p.name
    DESC

另外，上面的查询没有运行

最佳答案

你想让每个球员在小组赛中只拿到前20名的积分吗？
如果是，可以使用用户变量：-

SELECT
    p.pid,
    p.name,
    GROUP_CONCAT( gC.leaguepoints ORDER BY leaguepoints DESC ) AS leaguepoints,
    SUM(gC.leaguepoints) AS totalleaguepoints
FROM golf_player p
LEFT JOIN
(
    SELECT pid, leaguepoints, @Sequence:=IF(@PrevPid = pid, @Sequence + 1, 0) AS aSequence, @PrevPid := pid
    FROM
    (
        SELECT pid, leaguepoints
        FROM golf_card
        ORDER BY pid, leaguepoints DESC
    ) Sub1
    CROSS JOIN (SELECT @PrevPid := 0, @Sequence := 0) Sub2
) gC
ON p.pid = gC.pid AND aSequence < 20
GROUP BY p.pid
ORDER BY p.name DESC

关于php - secondSELECT语句的总和-PHP-MySQL，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/17544922/