functions - MySQL IZQUIERDA ÚNETE usando MAX & GROUP BY en la tabla unida?

mysql group by condition

Question

Tengo dos tablas (miembros y actividades) y estoy tratando de consultar a los miembros con la actividad más reciente para cada miembro. Lo tengo trabajando con dos consultas (una para obtener los miembros y una segunda con un máximo (id) y grupo por (miembro) en las actividades) y algo de código para combinar los datos. Estoy seguro de que se puede hacer con una sola consulta, pero no puedo resolverlo. ¿Algunas ideas?

mesa de miembros

id, name
 1, Shawn
 2, bob
 3, tom

mesa de actividades

id, member_id, code, timestamp, description
 1,         1,  123,     15000, baked a cake
 2,         1,  456,     20000, ate dinner
 3,         2,  789,     21000, drove home
 4,         1,  012,     22000, ate dessert

resultado deseado:

id, name,  activity_code, activity_timestamp, activity_description
 1, shawn, 012,           22000,              ate dessert
 2, bob,   789,           21000,              drove home
 3, tom,   null,          null,               null

Answer 1

El problema "más reciente por grupo" es extremadamente común en SQL. Hay innumerables ejemplos de soluciones a este mismo problema solo en este sitio.

Si sus sellos de tiempo son únicos por actividad de miembro:

SELECT
  m.id,
  m.name,
  a.code activity_code,
  a.timestamp activity_timestamp,
  a.description activity_description
FROM
  members m
  INNER JOIN activities a ON a.member_id = m.id
WHERE
  a.timestamp = (SELECT MAX(timestamp) FROM activities WHERE member_id = m.id)

alternativamente, si su ID de actividad aumenta monótonamente con el tiempo:

  ...
WHERE
  a.id = (SELECT MAX(id) FROM activities WHERE member_id = m.id)

No necesitas agrupar. Pero la consulta se beneficiará de un índice de activities sobre (member_id, timestamp) o (member_id, id) , respectivamente.

EDITAR

Para mostrar a los miembros que no hayan registrado una actividad, use una combinación izquierda como esta.

SELECT
  m.id,
  m.name,
  a.code activity_code,
  a.timestamp activity_timestamp,
  a.description activity_description
FROM
  members m
  LEFT JOIN activities a ON 
    a.member_id = m.id
    AND a.timestamp = (SELECT MAX(timestamp) FROM activities WHERE member_id = m.id)

Tenga en cuenta que no hay ninguna cláusula WHERE . Semánticamente, WHERE se aplica después de que se realizan las uniones. Por lo tanto, una cláusula WHERE eliminaría las filas que agregó la IZQUIERDA IZQUIERDA, dando efectivamente el mismo resultado que la JUNTA INTERNA original.

Pero si aplica el predicado adicional justo en la condición de unión, la IZQUIERDA IZQUIERDA funcionará como se esperaba.

Answer 2

SELECT 
    members.id ,
    members.name,
    activities.code AS activity_code,
    activities.timestamp AS activity_timestamp,
    activities.description AS activity_description
FROM 
    members
    LEFT JOIN activities
        ON members.id = activities.member_id
    LEFT JOIN 
        (
            SELECT
                activities.member_id
                MAX(activities.id) AS id
            FROM activities
            GROUP BY 
                activities.member_id
        ) AS t1
        ON activities.id = t1.id
WHERE
    t1.id IS NOT NULL

Answer 3

Select max(a.id), m.name, a.activity_code, a.activity_timestamp, a.activity_description
From members m
     Left join
     activities a on a.member_id=m.id
Group by  m.name, a.activity_code, a.activity_timestamp, a.activity_description