MySQL每个用户的第二个最大值

Question

已关闭。此问题不符合 Stack Overflow guidelines 。目前不接受答案。

---

要求提供代码的问题必须表现出对所解决问题的最低限度的了解。包括尝试的解决方案、为什么它们不起作用以及预期结果。另请参阅:Stack Overflow question checklist

已关闭 9 年前。

Improve this question

在 MySQL 表中有两列:用户和分数。

如何获得每个用户得分第二高的表格？这些第二个最大值中的最大值是多少？

换句话说，我可以有这个表:

User | Score
X      50
Y      74
X      9
X      12
Y      21

我想要一个具有第二个最大值的表格:

User | Score
X    | 12
Y    | 21

另一个具有第二个最大值的最大值:

Score
21

注意:性能很重要。我会认为最快的解决方案是正确的。

Answer 1

DROP TABLE IF EXISTS scores;

CREATE TABLE scores(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,user CHAR(1) NOT NULL,score INT NOT NULL,INDEX(user,score));

INSERT INTO scores (user,score)
VALUES 
('X',50),
('Y',74),
('X',9),
('X',12),
('Y',21);

SELECT x.* 
  FROM scores x 
  JOIN scores y 
    ON y.user = x.user 
   AND y.score >= x.score 
 GROUP 
    BY x.user
     , x.score 
HAVING COUNT(*) = 2 
 ORDER 
    BY score DESC 
 LIMIT 1;

+----+------+-------+
| id | user | score |
+----+------+-------+
|  5 | Y    |    21 |
+----+------+-------+

如果同一用户有可能两次获得相同的分数，那么您可能需要在其中的某处包含一个 DISTINCT - 但我会将其作为读者的练习。

如果性能是一个问题，那么类似于以下的解决方案具有无限的可扩展性。我说“相似”是因为我有在尝试跟踪多个变量时变得困惑的习惯......

SELECT id,user,score
  FROM
     (
     SELECT id
     , user
     , score
     , @puser := @cuser
     , @prev := @curr
     , @cuser := user
     , @curr := score
     , @rank := IF(@puser = @cuser,IF(@prev = @curr, @rank, @rank+1),@rank:=1) rank
  FROM scores
  JOIN (SELECT @cuser :=null,@puser := null,@curr := null, @prev := null, @rank := 0) sel1
 ORDER 
    BY user, score DESC
    ) x
  WHERE rank = 2
  ORDER BY score DESC LIMIT 1;

+----+------+-------+
| id | user | score |
+----+------+-------+
|  5 | Y    |    21 |
+----+------+-------+

对两个用户和 ca 的索引表进行快速测试。 10,000 行。查询 1 在 15 秒内完成，而查询 2 在 0.1 秒内完成!