Comment joindre la ligne la plus récente d'un tableau à un autre tableau ?

Question

J'ai des données qui ressemblent à ceci :

entities
id         name
1          Apple
2          Orange
3          Banana

Périodiquement, un processus s'exécute et attribue une note à chaque entité. Le processus génère les données et les ajoute à un tableau de scores comme suit :

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

Je souhaite pouvoir sélectionner toutes les entités ainsi que le score le plus récent enregistré pour chacune d'entre elles, ce qui permet d'obtenir des données comme celles-ci :

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

Je peux obtenir les données d'une seule entité à l'aide de cette requête :

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

Mais je ne sais pas comment sélectionner la même chose pour toutes les entités. Peut-être est-ce que cela me saute aux yeux ?

Merci beaucoup d'avoir pris le temps.

Merci pour ces excellentes réponses. Je vais laisser passer quelques jours pour voir si une solution préférée se dessine, puis je sélectionnerai la réponse.

MISE A JOUR : J'ai essayé plusieurs des solutions proposées, le principal problème auquel je suis confronté est que si une entité n'a pas encore de score généré, elle n'apparaît pas dans la liste.

À quoi ressemblerait le code SQL pour s'assurer que toutes les entités sont renvoyées, même si elles n'ont pas encore de score affiché ?

MISE À JOUR : Réponse sélectionnée. Merci à tous !

Answer 1

Je procède de la manière suivante :

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;

Answer 2

J'ajouterai simplement une variante à ce sujet :

SELECT e.*, s1.score
FROM entities e
INNER JOIN score s1 ON e.id = s1.entity_id
WHERE NOT EXISTS (
    SELECT 1 FROM score s2 WHERE s2.id > s1.id
)

Answer 3

approche 1

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

INNER  JOIN scores
ON entities.id = scores.entity_id

WHERE scores.date_added = 
  (SELECT max(date_added) FROM scores where entity_id = entities.id)

Answer 4

approche 2

coût de l'interrogation par rapport au lot :

• approche 1 : 22% -- plus rapide

• approche 2 : 22% -- plus rapide

• approche 3 : 27%

• approche 4 : 27%

---

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

INNER  JOIN scores
ON entities.id = scores.entity_id

inner join 
    (
    SELECT 
           entity_id, max(date_added) as recent_date
    FROM scores
    group by entity_id
    ) as y on entities.id = y.entity_id and scores.date_added = y.recent_date

Answer 5

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

INNER  JOIN scores
ON entities.id = scores.entity_id

WHERE entities.id in 
(select id from scores s2 where date_added = max(date_added) and s2.id = entities.id)

ORDER BY scores.date_added DESC
LIMIT 1