mysql - 查找用户最高分和相关详细信息

Question

我有一张表，用户可以在其中存储分数和有关该分数的其他信息(例如分数注释或所用时间等)。我想要一个 mysql 查询来查找每个用户的个人最佳分数及其相关的笔记和时间等。

我尝试使用的是这样的:

SELECT *, MAX(score) FROM table GROUP BY (user)

问题在于，虽然您可以从该查询 [MAX(score)] 中获取用户个人最佳成绩，但返回的音符和时间等与最高分数无关，而是一个不同的分数(特别是包含的分数在 *)。有没有一种方法可以编写一个查询来选择我想要的内容？还是我必须在 PhP 中手动完成？

Answer 1

我假设您只希望每位玩家获得一个结果，即使他们不止一次获得相同的最高分。我还假设您希望每个玩家第一次获得他们的个人最好成绩，以防出现重复。

有几种方法可以做到这一点。这是 MySQL 特有的方法:

SELECT user, scoredate, score, notes FROM (
    SELECT *, @prev <> user AS is_best, @prev := user
    FROM table1, (SELECT @prev := -1) AS vars
    ORDER BY user, score DESC, scoredate
) AS T1
WHERE is_best

下面是使用普通 SQL 的更通用的方法:

SELECT T3.* FROM table1 AS T3
JOIN (
    SELECT T1.user, T1.score, MIN(scoredate) AS scoredate
    FROM table1 AS T1
    JOIN (SELECT user, MAX(score) AS score FROM table1 GROUP BY user) AS T2
    ON T1.user = T2.user AND T1.score = T2.score
    GROUP BY T1.user
) AS T4
ON T3.user = T4.user AND T3.score = T4.score AND T3.scoredate = T4.scoredate

结果:

1, '2010-01-01 17:00:00', 50, 'Much better'
2, '2010-01-01 14:00:00', 100, 'Perfect score'

我用来测试这个的测试数据:

CREATE TABLE table1 (user INT NOT NULL, scoredate DATETIME NOT NULL, score INT NOT NULL, notes NVARCHAR(100) NOT NULL);
INSERT INTO table1 (user, scoredate, score, notes) VALUES
(1, '2010-01-01 12:00:00', 10, 'First attempt'),
(1, '2010-01-01 17:00:00', 50, 'Much better'),
(1, '2010-01-01 22:00:00', 30, 'Time for bed'),
(2, '2010-01-01 14:00:00', 100, 'Perfect score'),
(2, '2010-01-01 16:00:00', 100, 'This is too easy');