¿Cómo puedo unir la fila más reciente de una tabla a otra tabla?
Tengo datos que se ven así:
entities
id name
1 Apple
2 Orange
3 Banana
Periódicamente, un proceso se ejecutará y dará una puntuación a cada entidad. El proceso genera los datos y los agrega a una tabla de puntuaciones de la siguiente manera:
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
Quiero poder seleccionar todas las entidades junto con la puntuación registrada más reciente para cada una, lo que resulta en algunos datos como este:
entities
id name score date_added
1 Apple 15 1/4/09
2 Orange 15 1/4/09
3 Banana 15 1/4/09
Puedo obtener los datos de una sola entidad usando esta consulta:
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
Pero no sé cómo seleccionar lo mismo para todos entidad. ¿Tal vez me está mirando a la cara?
Gracias muy amablemente por tomarse el tiempo.
Gracias por las grandes respuestas. Le daré unos días para ver si una solución preferida burbujea y luego seleccionaré la respuesta.
ACTUALIZACIÓN: He probado varias de las soluciones propuestas, el principal problema al que me enfrento ahora es que si una entidad aún no tiene una puntuación generada, no aparece en la lista.
Cómo se vería el SQL para garantizar que todas las entidades son devueltos, incluso si no tienen ninguna puntuación publicada todavía?
ACTUALIZACIÓN: Respuesta seleccionada. Gracias a todos!
7 answers
Lo hago de esta manera:
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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-01-30 22:36:46
Solo para agregar mi variación sobre él:
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
)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-01-31 03:45:01
Enfoque 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)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-01-31 04:21:30
Enfoque 2
Costo de consulta relativo al lote:
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 12:34:31
Sé que esta es una vieja pregunta, solo pensé en agregar un enfoque que nadie ha mencionado todavía, Cross Apply o Outer Apply. Estos están disponibles en SQL Server 2005 (el tipo de base de datos no está etiquetado en esta pregunta) o superior
Usando las tablas temporales
DECLARE @Entities TABLE(Id INT PRIMARY KEY, name NVARCHAR(MAX))
INSERT INTO @Entities
VALUES (1, 'Apple'), (2, 'Orange'), (3, 'Banana'), (4, 'Cherry')
DECLARE @Scores TABLE(Id INT PRIMARY KEY, Entity_Id INT, Score INT, Date_Added DATE)
INSERT INTO @Scores
VALUES (1,1,10,'2009-02-01'),
(2,2,10,'2009-02-01'),
(3,1,15,'2009-02-01'),
(4,2,10,'2009-03-01'),
(5,1,15,'2009-04-01'),
(6,2,15,'2009-04-01'),
(7,3,22,'2009-04-01')
Podrías usar
SELECT E.Id, E.name, S.Score, S.Date_Added
FROM @Entities E
CROSS APPLY
(
SELECT TOP 1 *
FROM @Scores Sc
WHERE Sc.Entity_Id = E.Id
ORDER BY sc.Score DESC
) AS S
Para obtener los resultados deseados. El equivalente para permitir a las entidades sin puntuaciones sería
SELECT E.Id, E.name, S.Score, S.Date_Added
FROM @Entities E
OUTER APPLY
(
SELECT TOP 1 *
FROM @Scores Sc
WHERE Sc.Entity_Id = E.Id
ORDER BY sc.Score DESC
) AS S
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-10-10 14:44:28
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-01-31 02:48:37
También puede hacer esto hoy en día en la mayoría de RDBMSs (Oracle, PostgreSQL, SQL Server) con una consulta natural utilizando funciones de ventana como ROW_NUMBER:
SELECT id, name, score, date_added FROM (
SELECT e.id, e.name, s.score, s.date_added,
ROW_NUMBER() OVER (PARTITION BY e.id ORDER BY s.date_added DESC) rn
FROM Entities e INNER JOIN Scores s ON e.id = s.entity_id
) tmp WHERE rn = 1;
Violín SQL
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-12-23 23:54:54