SQL按两个字段分组和排序

Question

可能是重复的问题:
按GROUP BY排序的SQL ORDER BY总和

更新：我已经找到了我的解决方案，我已经在这里发布了。感谢大家的帮助！

---

我正在开发一个需要排行榜的Facebook应用程序。得分和完成游戏所需时间会被记录下来，首先按照得分进行排序，如果有两个相同的得分，则使用时间进行排序。如果用户多次玩游戏，则使用他们最好的成绩。

游戏表现越好，得分就越低。

我的表格结构如下：

id
facebook_id - (Unique Identifier for the user)
name
email
score
time - (time to complete game in seconds)
timestamp - (unix timestamp of entry)
date - (readable format of timestamp)
ip

我认为应该可行的查询是：

SELECT *
FROM entries
ORDER BY score ASC, time ASC
GROUP BY facebook_id

我遇到的问题是在某些情况下，它会从数据库中拉取用户的第一次得分，而不是他们的最高得分。我认为这是由于GROUP BY语句造成的。我原本以为ORDER BY语句可以解决这个问题，但显然并不行。

例如：

----------------------------------------------------------------------------
|  ID  |       NAME       |  SCORE  |  TIME  |  TIMESTAMP  |  DATE  |  IP  |
----------------------------------------------------------------------------
|  1   |  Joe Bloggs      |  65     |   300  | 1234567890  |  XXX   |  XXX |
----------------------------------------------------------------------------
|  2   |  Jane Doe        |  72     |   280  | 1234567890  |  XXX   |  XXX |
----------------------------------------------------------------------------
|  3   |  Joe Bloggs      |  55     |   285  | 1234567890  |  XXX   |  XXX |
----------------------------------------------------------------------------
|  4   |  Jane Doe        |  78     |   320  | 1234567890  |  XXX   |  XXX |
----------------------------------------------------------------------------

当我使用上述查询时，我得到以下结果：

 1. Joe Bloggs - 65 - 300 - (Joes First Entry, not his best entry) 
 2. Jane Doe - 72 - 280

我本来期望...

 1. Joe Bloggs - 55 - 285 - (Joe's best entry)
 2. Jane Doe - 72 - 280 

这就像是 Group By 忽略了 Order - 并且只是覆盖值。

使用 MIN(score) 和 group by 一起选择最低分数是正确的 - 然而它合并了用户在数据库中的第一条记录的时间，因此经常返回不正确的结果。

那么，如何选择用户的最高分数和关联的时间、名称等，并按照分数和时间排序结果？

提前感谢！

- Darren Craig

---

“WHERE”条件非常重要。在使用“GROUP BY”时，引擎有权选择来自分组项的任何列，甚至可以从每个列的不同行中选择。 - biziclop

"ORDER BY" 在 "GROUP BY" 之后。 - vyegorov

请使用多列的UNIQUE索引。 - hjpotter92

请提供样本数据和期望的输出。 - D'Arcy Rittich

@RedFilter - 更新了问题并包含了一些样本数据。T-Shirt Dude - 我不确定在这种情况下唯一索引会有什么作用。Teran - 不幸的是，当我尝试这样做时，Group By语句将数据库中的第一次合并到返回的行中，因此它没有返回正确的数据。 - Darren Craig

Answer 1

您的查询实际上没有意义，因为 order by 应该在 group by 之后。您使用的是哪个 SQL 引擎？大多数引擎都会报错。

我认为您想要的更像：

select e.facebookid, minscore, min(e.time) as mintime -- or do you want maxtime?
from entries e join
     (select e.facebookid, min(score) as minscore
      from entries e
      group by facebookid
     ) esum
     on e.facebookid = esum.facebookid and
        e.score = e.minscore
group by e.facebookid, minscore

你也可以使用窗口函数来实现这一点，但这取决于你的数据库。

- Gordon Linoff

---

抱歉，我之前的说法是错误的 - Order By 在 Group By 之后。我会尝试一下这个方法。 - Darren Craig

Answer 2

您需要将分数降到最低。

    SELECT
      facebook_id,
      name,
      email,
      min(score) as high_score
    FROM
      entries
    GROUP BY
      facebook_id,
      name,
      email
   ORDER BY
     min(score) ASC

- Phil

---

嗨@Phil - 我今天尝试了这个方法和几个变化。不幸的是，它返回用户所做的每个条目 - 它没有选择每个用户的最高分数。 - Darren Craig

从group by和select中删除ID和时间。对于刚才的回答，我很抱歉，我会进行编辑。 - Phil

Answer 3

一个方法是这样的：

SELECT entries.facebook_id, MIN(entries.score) AS score, MIN(entries.time) AS time
FROM entries
    INNER JOIN (
        SELECT facebook_id, MIN(score) AS score
        FROM entries
        GROUP BY facebook_id) highscores
    ON highscores.facebook_id = entries.facebook_id
    AND entries.score = highscores.score
GROUP BY entries.facebook_id
ORDER BY MIN(entries.score) ASC, MIN(entries.time) ASC

如果您需要从条目表中获取更多信息，可以将其用作子查询，并再次加入呈现的信息（facebook_id、得分、时间）以获取每个用户的一行。

这是关键所在：您需要进行两次聚合，一次查找用户的最低得分，再一次查找该用户和得分的最低时间。您可以颠倒聚合的顺序，但我预计这将过滤得最快，因此最有效。

您还可以检查哪种方式更快，第二次聚合：使用最低得分还是使用得分进行分组。

- penguat

Answer 4

感谢您的帮助。 @Penguat 提供了最接近答案...这是我的最终查询，供有同样问题的人参考...

SELECT f.facebook_id, f.name, f.score, f.time FROM
    (SELECT facebook_id, name, min(score)
    AS highscore FROM golf_entries
    WHERE time > 0
    GROUP BY facebook_id)
AS x
INNER JOIN golf_entries as f
ON f.facebook_id = x.facebook_id
AND f.score = x.highscore
ORDER BY score ASC, time ASC

再次感谢您！

- Darren Craig

Answer 5

如果您想要他们的最佳时间，您需要使用MIN()函数 - 您说得越低分数，他们做得越好。

SELECT facebook_id, MIN(score), time, name, ... 
FROM entries 
GROUP BY facebook_id, time, name, ...
ORDER BY score, time

- Anthony Grist

---

谢谢您的回复。不过这还不是我想要的——它会按顺序输出所有参赛者的所有分数。我只需要每个用户的最高分和相应的时间。我已经在和它斗争了一整天……我相信我肯定错过了某些非常简单的东西。 - Darren Craig

如果用户有两个相同的分数，但时间不同，哪个时间更好 - 更高还是更低？ - Anthony Grist