如何将表中的最新行加入到另一个表中？ [英] 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

我可以使用此查询获取单个实体的数据： / p>

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?

非常感谢你花时间。

感谢您的伟大的回应我会给它几天，看看一个首选解决方案是否会起泡，然后我会选择答案。

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屋！