mysql - 为每个用户选择前三名分数的总和

Question

我在为以下问题编写查询时遇到问题。我已经尝试了一些现有的查询，但无法获得我需要的结果。

我有一个这样的结果表:

userid  score timestamp
1       50    5000
1       100   5000 
1       400   5000 
1       500   5000 
2       100   5000 
3       1000  4000 

查询的预期输出是这样的:

userid  score
3       1000
1       1000
2       100

我想选择一个顶部列表，其中我为每个用户汇总了 n 个最佳分数，如果有平局，则时间戳最低的用户最高。我确实尝试查看所有旧帖子，但找不到对我有帮助的帖子。

这是我尝试过的:

SELECT sum(score) FROM (
   SELECT score 
   FROM results 
   WHERE userid=1 ORDER BY score DESC LIMIT 3
) as subquery

这给了我一个用户的结果，但我想要一个按顺序获取所有结果的查询。

Answer 1

这是一个非常典型的 greatest-n-per-group问题。当我看到这些时，我通常使用这样的相关子查询:

SELECT *
FROM myTable m
WHERE(
  SELECT COUNT(*)
  FROM myTable mT
  WHERE mT.userId = m.userId AND mT.score >= m.score) <= 3;

这不是完整的解决方案，因为它只为您提供其所在行中每个用户的前三名得分。要获得总数，您可以使用 SUM() 包裹子查询，如下所示:

SELECT userId, SUM(score) AS totalScore
FROM(
  SELECT userId, score
  FROM myTable m
  WHERE(
    SELECT COUNT(*)
    FROM myTable mT
    WHERE mT.userId = m.userId AND mT.score >= m.score) <= 3) tmp
GROUP BY userId;

这是一个 SQL Fiddle示例。

编辑

关于排序(我第一次忘记了)，您可以按 totalScore 降序排序，然后按 MIN(timestamp) 升序排序，这样具有最低时间戳的用户将首先出现在列表中。这是更新后的查询:

SELECT userId, SUM(score) AS totalScore
FROM(
  SELECT userId, score, timeCol
  FROM myTable m
  WHERE(
    SELECT COUNT(*)
    FROM myTable mT
    WHERE mT.userId = m.userId AND mT.score >= m.score) <= 3) tmp
GROUP BY userId
ORDER BY totalScore DESC, MIN(timeCol) ASC;

这是更新的 Fiddle链接。

编辑 2

正如 JPW 在评论中指出的那样，如果用户对多个问题的得分相同，则此查询将不起作用。要解决这个问题，您可以在子查询中添加一个附加条件，以按时间戳为用户排序三行，如下所示:

SELECT userId, SUM(score) AS totalScore
FROM(
  SELECT userId, score, timeCol
  FROM myTable m
  WHERE(
    SELECT COUNT(*)
    FROM myTable mT
    WHERE mT.userId = m.userId AND mT.score >= m.score 
      AND mT.timeCol <= m.timeCol) <= 3) tmp
GROUP BY userId
ORDER BY totalScore DESC, MIN(timeCol) ASC;

我仍在研究解决方案，以找出如何处理用户标识、分数和时间戳都相同的情况。在这种情况下，您将不得不寻找另一个决胜局。也许你有一个主键列，你可以选择取更高/更低的主键？