在MySQL中连接一张表的单行数据

Question

我有两个表格players和scores。

我想生成一个报告，大致如下：

player    first score             points
foo       2010-05-20              19
bar       2010-04-15              29
baz       2010-02-04              13

现在，我的查询看起来像这样：

select p.name        player,
       min(s.date)   first_score,
       s.points      points    
from  players p    
join  scores  s on  s.player_id = p.id    
group by p.name, s.points

我需要与 min(s.date) 返回的行相关联的 s.points。这个查询是否可以实现？也就是说，如何确保我获取了正确的连接行的 s.points 值？

顺便说一下：我想象这与 MySQL 缺乏稠密排名有关。有什么最好的解决办法呢？

- maček

Answer 1

这是一个在Stack Overflow上经常出现的最大N组问题。

这是我的通常回答：

select
  p.name        player,
  s.date        first_score,
  s.points      points

from  players p

join  scores  s
  on  s.player_id = p.id

left outer join scores  s2
  on  s2.player_id = p.id
      and s2.date < s.date

where
  s2.player_id is null

;

换句话说，给定分数s，尝试找到同一玩家的早期日期的分数s2。如果没有找到早期的分数，则s是最早的。

---

关于平局的评论：您必须有一个政策来决定在平局的情况下使用哪一个。一种可能性是，如果使用自增主键，则最小值的那个是较早的。请参见下面外部连接中的附加术语：

select
  p.name        player,
  s.date        first_score,
  s.points      points

from  players p

join  scores  s
  on  s.player_id = p.id

left outer join scores  s2
  on  s2.player_id = p.id
      and (s2.date < s.date or s2.date = s.date and s2.id < s.id)

where
  s2.player_id is null

;

基本上，您需要添加打破平局的条件，直到找到一个保证对于给定玩家至少是唯一的列。表的主键通常是最佳解决方案，但我曾经见过其他列也适合的情况。

关于我与@OMG Ponies分享的评论，请记住这种类型的查询极大地受益于正确的索引。

- Bill Karwin

---

+1：有没有可能比较一下你的方法和我的方法？我很好奇哪个更有效率，但我倾向于你的方法，因为MySQL处理LEFT JOIN/IS NULL的方式。 - OMG Ponies

如果我的 join scores s... 比 s.player_id = p.id 有更多的关联条件，那么在 left outer join scores s2... 中我是否需要复制所有这些条件？ - maček

@OMG Ponies：我发现在MySQL中使用GROUP BY会影响性能，因为MySQL几乎总是会创建一个临时表。而使用外连接解决方案（或等效的带有相关子查询的NOT EXISTS），可以使用覆盖索引，因此连接可以在内存中完成。 - Bill Karwin

@macek：是的，连接到s2必须使用与连接到s相同的条件，再加上比较日期的条件。如果存在并列（在同一日期有多个分数）的可能性，则可能需要额外的连接项来解决并列。 - Bill Karwin

你说得很对！对于一些用户，因为他们在首次游戏时有2-5个分数落在第一天，所以会返回多行。该如何解决呢？ - maček

Answer 2

大多数关系型数据库在使用GROUP BY时，甚至不允许您在SELECT子句中包含非聚合列。在MySQL中，您将得到来自非聚合列的随机行的值。如果您实际上对于所有行都有相同的特定列值，则这是有用的。因此，很好的是MySQL没有限制我们，尽管这是一个重要的事情需要理解。

SQL Antipatterns专门为此撰写了一整章节。

- Marcus Adams

---

谢谢Marcus！ :) 此外，您可以使用 SET SQL_MODE = ONLY_FULL_GROUP_BY 使MySQL的行为更加标准化。 - Bill Karwin

巧合的是，@Bill Karwin（回答了这个问题的那位作者）恰好也是那本书的作者！世界真小 :) - maček