Problemas con el mayor n-por-grupo de MySQL

Hola a todos. Creo que esta es una pregunta de 'gran n-por-grupo', pero incluso después de ver varias preguntas sobre StackOverflow, no estoy seguro de cómo aplicar esto a mi situación …

Estoy usando una database MySQL y tengo un sistema básico tipo blog configurado sobre Aplicaciones de Computadora … Las tablas se ven así:

POSTS post_id post_created post_type -- could be article, review, feature, whatever post_status -- 'a' approved or 'd' for draft APPS app_id app_name app_platform -- Windows, linux, unix, etc.. APP_TO_POST -- links my posts to its relevant application atp_id atp_app_id atp_post_id 

Estoy usando la siguiente consulta básica para extraer todos los artículos de la aplicación con el nombre 'Photoshop', donde el tipo de publicación es un 'Artículo' y el estado del artículo es 'a' para aprobado:

 SELECT apps.app_name, apps.app_platform, posts.post_created, posts.post_id FROM apps JOIN app_to_post ON app_to_post.atp_app_id = apps.app_id JOIN posts ON app_to_post.atp_post_id = posts.post_id WHERE apps.app_name = 'Photoshop' AND posts.post_type = 'Article' AND posts.post_status = 'a' 

Lo cual me da los resultados esperados:

 app_name app_platform post_created post_id Photoshop Windows Oct. 20th, 2009 1 Photoshop Windows Dec. 1, 2009 3 Photoshop Macintosh Nov. 10th, 2009 2 

¿Alguien podría darme una mano sobre cómo podría alterar esa consulta para extraer solo el artículo más reciente por plataforma de aplicaciones? Entonces, por ejemplo, me gustaría que mis resultados se vean así:

 app_name app_platform post_created post_id Photoshop Windows Dec. 1, 2009 3 Photoshop Macintosh Nov. 10th, 2009 2 

Y omita uno de los artículos de 'Photoshop Windows' porque no es el más reciente.

Si simplemente me MAX(post_created) en un MAX(post_created) y en un GROUP BY app_platform mis resultados no siempre se agrupan correctamente. Por lo que entiendo, ¿necesito realizar algún tipo de combinación interna de una sub consulta?

Como tiene muchos JOIN s, sugiero crear primero una VIEW :

 CREATE VIEW articles AS SELECT a.app_name, a.app_platform, p.post_created, p.post_id FROM apps a JOIN app_to_post ap ON ap.atp_app_id = a.app_id JOIN posts p ON ap.atp_post_id = p.post_id WHERE p.post_type = 'Article' AND p.post_status = 'a'; 

Entonces puedes usar un NULL-self-join:

 SELECT a1.app_name, a1.app_platform, a1.post_created, a1.post_id FROM articles a1 LEFT JOIN articles a2 ON a2.app_platform = a1.app_platform AND a2.post_created > a1.post_created WHERE a2.post_id IS NULL; 

Caso de testing:

 CREATE TABLE posts ( post_id int, post_created datetime, post_type varchar(30), post_status char(1) ); CREATE TABLE apps ( app_id int, app_name varchar(40), app_platform varchar(40) ); CREATE TABLE app_to_post ( atp_id int, atp_app_id int, atp_post_id int ); INSERT INTO posts VALUES (1, '2010-10-06 05:00:00', 'Article', 'a'); INSERT INTO posts VALUES (2, '2010-10-06 06:00:00', 'Article', 'a'); INSERT INTO posts VALUES (3, '2010-10-06 07:00:00', 'Article', 'a'); INSERT INTO posts VALUES (4, '2010-10-06 08:00:00', 'Article', 'a'); INSERT INTO posts VALUES (5, '2010-10-06 09:00:00', 'Article', 'a'); INSERT INTO apps VALUES (1, 'Photoshop', 'Windows'); INSERT INTO apps VALUES (2, 'Photoshop', 'Macintosh'); INSERT INTO app_to_post VALUES (1, 1, 1); INSERT INTO app_to_post VALUES (1, 1, 2); INSERT INTO app_to_post VALUES (1, 2, 3); INSERT INTO app_to_post VALUES (1, 2, 4); INSERT INTO app_to_post VALUES (1, 1, 5); 

Resultado:

 +-----------+--------------+---------------------+---------+ | app_name | app_platform | post_created | post_id | +-----------+--------------+---------------------+---------+ | Photoshop | Macintosh | 2010-10-06 08:00:00 | 4 | | Photoshop | Windows | 2010-10-06 09:00:00 | 5 | +-----------+--------------+---------------------+---------+ 2 rows in set (0.00 sec) 

Como nota al margen, en general, no necesita una key sustituta para su tabla de unión . También podría configurar una key primaria compuesta (y, idealmente, keys externas a las tablas referencedas):

 CREATE TABLE app_to_post ( atp_app_id int, atp_post_id int, PRIMARY KEY (atp_app_id, atp_post_id), FOREIGN KEY (atp_app_id) REFERENCES apps (app_id), FOREIGN KEY (atp_post_id) REFERENCES posts (post_id) ) ENGINE=INNODB; 

Primero consideremos cómo get filas con el valor máximo del resultado de su consulta y su resultado deseable:

Su resultado: (llamémoslo tabla T)

 app_name app_platform post_created post_id Photoshop Windows Oct. 20th, 2009 1 Photoshop Windows Dec. 1, 2009 3 Photoshop Macintosh Nov. 10th, 2009 2 

El resultado que quieres:

 app_name app_platform post_created post_id Photoshop Windows Dec. 1, 2009 3 Photoshop Macintosh Nov. 10th, 2009 2 

Para get el resultado, debes:

  1. Calcule el máximo post_id para cada plataforma para la tabla T.
  2. Unir el resultado máximo con la tabla de origen T para get valores en otras columnas de la fila.

La consulta está abajo:

 SELECT t1.app_name,t1.app_platform,t1.post_created,t1.post_id FROM (SELECT app_platform, MAX(post_created) As MaxPostCreated FROM T GROUP BY app_platform) AS t2 JOIN T AS t1 WHERE t1.app_platform = t2.app_platform1 AND t2.MaxPostCreated = t1.post_created 

En esta consulta, la subconsulta realizó el primer paso y join realiza el segundo paso.

El resultado final que combina con su respuesta parcial se muestra a continuación (con una vista):

 CREATE VIEW T SELECT a.app_name, a.app_platform, p.post_created, p.post_id FROM apps a JOIN app_to_post ap ON ap.atp_app_id = a.app_id JOIN posts p ON ap.atp_post_id = p.post_id WHERE p.post_type = 'Article' AND p.post_status = 'a'; SELECT t1.app_name,t1.app_platform,t1.post_created,t1.post_id FROM (SELECT app_platform, MAX(post_created) As MaxPostCreated FROM T GROUP BY app_platform) AS t2 JOIN T AS t1 WHERE t1.app_platform = t2.app_platform1 AND t2.MaxPostCreated= t1.post_created 

Por cierto, nuestro equipo actualmente está desarrollando una herramienta que intenta ayudar automáticamente a los usuarios a escribir consultas, y los usuarios pueden proporcionar ejemplos de input y salida a la herramienta, y la herramienta generará una consulta. (¡La herramienta realmente genera la primera parte de la consulta! El enlace a nuestro prototipo es https://github.com/Mestway/Scythe )

Espero que esto le pueda ayudar. 🙂

Estás en el path correcto.

Prueba agregar

 group by app_name,app_platform having post_created=max(post_created) 

O si su post_id es secuencial, donde un valor más alto siempre reflejará una publicación posterior, use esta cláusula having post_id=max(post_id) : having post_id=max(post_id)