从MySQL中的表联接单行 [英] Join single row from a table in MySQL

问题描述

我有两个表players和scores.

我想生成一个看起来像这样的报告:

I want to generate a report that looks something like this:

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

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

Right now, my query looks something like this:

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值?

I need the s.points that is associated with the row that min(s.date) returns. Is that happening with this query? That is, how can I be certain I'm getting the correct s.points value for the joined row?

侧面说明:我想这与MySQL缺乏密集排名有关.最好的解决方法是什么?

Side note: I imagine this is somehow related to MySQL's lack of dense ranking. What's the best workaround here?

推荐答案

这是Stack Overflow上经常出现的每组最大的问题.

This is the greatest-n-per-group problem that comes up frequently on Stack Overflow.

这是我通常的答案:

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是最早的分数.

In other words, given score s, try to find a score s2 for the same player, but with an earlier date. If no earlier score is found, then s is the earliest one.

对领带的评论:您必须制定一项政策，以防领带被使用.一种可能是，如果您使用自动递增的主键，则值最小的一个是较早的一个.请参阅下面的外部联接中的附加术语:

Re your comment about ties: You have to have a policy for which one to use in case of a tie. One possibility is if you use auto-incrementing primary keys, the one with the least value is the earlier one. See the additional term in the outer join below:

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

;

基本上，您需要添加决胜局条款，直到进入保证至少对于给定玩家而言唯一的一列为止.该表的主键通常是最好的解决方案，但是我已经看到了另一列合适的情况.

Basically you need to add tiebreaker terms until you get down to a column that's guaranteed to be unique, at least for the given player. The primary key of the table is often the best solution, but I've seen cases where another column was suitable.

关于我与@OMG Ponies分享的评论，请记住，这种类型的查询可从正确的索引中受益匪浅.

Regarding the comments I shared with @OMG Ponies, remember that this type of query benefits hugely from the right index.

这篇关于从MySQL中的表联接单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！