如何将一个表中的最新行连接到另一个表? [英] How do I join the most recent row in one table to another table?

问题描述

我有这样的数据:

entities
id         name
1          Apple
2          Orange
3          Banana

定期运行一个流程并为每个实体打分.该过程生成数据并将其添加到分数表中，如下所示:

Periodically, a process will run and give a score to each entity. The process generates the data and adds it to a scores table like so:

scores 
id  entity_id    score   date_added
1    1            10       1/2/09
2    2            10       1/2/09
3    1            15       1/3/09
4    2            10       1/03/09
5    1            15       1/4/09
6    2            15       1/4/09
7    3            22       1/4/09

我希望能够选择所有实体以及每个实体的最新记录分数，从而产生如下数据:

I want to be able to select all of the entities along with the most recent recorded score for each resulting in some data like this:

entities
id name     score  date_added
1  Apple     15     1/4/09
2  Orange    15     1/4/09
3  Banana    15     1/4/09

我可以使用此查询获取单个实体的数据:

I can get the data for a single entity using this query:

SELECT entities.*, 
       scores.score, 
       scores.date_added 
FROM entities

INNER  JOIN scores
ON entities.id = scores.entity_id

WHERE entities.id = ?

ORDER BY scores.date_added DESC
LIMIT 1

但是我不知道如何为所有实体选择相同的.也许它正盯着我看?

But I'm at a loss for how to select the same for all entities. Perhaps it's staring me in the face?

非常感谢您抽出宝贵时间.

Thank you very kindly for taking the time.

感谢您的精彩回复.我会给它几天时间，看看是否有首选的解决方案出现，然后我会选择答案.

Thanks for the great responses. I'll give it a few days to see if a preferred solution bubbles up then I'll select the answer.

更新:我已经尝试了几个建议的解决方案，我现在面临的主要问题是，如果一个实体还没有生成的分数，它们就不会出现在列表中.

UPDATE: I've tried out several of the proposed solutions, the main issue I'm facing now is that if an entity does not yet have a generated score they don't appear in the list.

确保返回所有实体的 SQL 是什么样的，即使它们尚未发布任何分数?

What would the SQL look like to ensure that all entities are returned, even if they don't have any score posted yet?

更新:已选择答案.谢谢大家！

UPDATE: Answer selected. Thanks everyone!

推荐答案

我是这样做的:

SELECT e.*, s1.score, s1.date_added 
FROM entities e
  INNER JOIN scores s1
    ON (e.id = s1.entity_id)
  LEFT OUTER JOIN scores s2
    ON (e.id = s2.entity_id AND s1.id < s2.id)
WHERE s2.id IS NULL;

这篇关于如何将一个表中的最新行连接到另一个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！