SQL - получить последнюю запись в подмножестве подзапроса или в соединении, используя GROUP BY

Question

Я уже некоторое время ломаю голову над этим и понимаю, что пришло время обратиться за помощью. В рамках интеграции со сторонней системой я добавил в существующую систему дополнительную таблицу для управления синхронизацией записей. Я уменьшу структуру таблиц, чтобы просто включить достаточно деталей, чтобы представить проблему:

Table 1: data
Columns: (int)data_id*, (varchar)name, (datetime)date_created

Table 2: sync
Columns: (int)sync_id*, (int)data_id, (int)result, (varchar)details,
         (datetime)date_created

* denotes primary index

Когда сторонняя система пытается синхронизировать записи, она использует HTTP-запрос GET и серверный скрипт возвращает XML-ответ, содержащий информацию о записи, которая ожидает синхронизации в первый раз (для этого data_id не будет записей синхронизации) а также записать информацию, которая ожидает повторной попытки при синхронизации, которая потерпела неудачу при предыдущей попытке (самая последняя запись синхронизации для этого data_id будет иметь значение результата 0). Затем сторонняя система использует HTTP-запрос POST к другому серверному сценарию, который обеспечивает обратную связь относительно того, какие записи ему удалось сопоставить и успешно синхронизировать (result = 1) и какие записи не удалось сопоставить и синхронизировать (result = 0, details = "Error Message").

Для каждой записи данных часто будет несколько записей синхронизации, поскольку существует ряд допустимых сценариев, которые могут помешать успешной синхронизации, если пользователь сначала не предпримет каких-либо действий.

Важно вести журнал каждой попытки синхронизации, и поэтому просто добавлять столбец синхронизации в таблицу данных недопустимо.

Версия с псевдокодом о том, что я пытаюсь заставить работать с SQL, выглядит следующим образом:

1. Получить все записи из таблицы данных и рядом с каждой записью.
2. И для каждой из этих записей найдите самую последнюю запись синхронизации, сопоставив data_id, упорядочив записи синхронизации в порядке убывания (самые новые вверху) и ограничив записи синхронизации только 1 (нам нужна только самая последняя запись синхронизации для этого запроса).
3. Показать столбцы из данных и последней записи синхронизации. Если запись синхронизации не существует, запись данных все равно должна отображаться, а столбцы синхронизации просто заполняются значениями NULL.

Ближайший рабочий оператор SQL, который у меня есть, использует LEFT JOIN:

SELECT d.data_id, d.name, d.date_created, s.sync_id, s.result, s.details
FROM (
    SELECT data_id, name, date_created
    FROM data
) AS d 
LEFT JOIN (
    SELECT sync_id, data_id, result, details, date_created
    FROM sync 
    GROUP BY data_id
) AS s 
ON d.data_id = s.data_id 
ORDER BY d.date_created DESC;

К сожалению, это не похоже на самую последнюю запись синхронизации, но GROUP BY data_id кажется, просто захватывает первую найденную запись синхронизации. Синтаксис MySQL не позволит мне разместить ORDER BY date_created DESC перед GROUP BY data_id линия. Если я помещу этот оператор ORDER BY после строки GROUP BY, то это, похоже, не даст никакого эффекта, и самая последняя запись синхронизации не будет показана рядом со столбцами данных в результатах.

Более простая версия, с которой я начинал, имеет ту же самую проблему - не обязательно брать самую последнюю запись синхронизации:

SELECT d.data_id, d.name, d.date_created, s.result, s.details 
FROM data AS d LEFT JOIN sync AS s ON d.data_id = s.data_id 
WHERE s.result = 0 OR s.result IS NULL;

Я также попытался использовать подзапрос для достижения этой же проблемы:

SELECT d.data_id, d.name, d.date_created, s.sync_id, s.result, s.details
FROM (
    SELECT data_id, name, date_created
    FROM data
) AS d, (
    SELECT s.sync_id, s.data_id, s.result, s.details, s.date_created
    FROM sync AS s, data AS d
    WHERE s.data_id = d.data_id
    ORDER BY s.date_created DESC
) AS s 
WHERE d.data_id = s.data_id 
ORDER BY s.date_created DESC;

Кто-нибудь может посоветовать, как я могу быть уверен, что получу только самую последнюю запись синхронизации вместе с записями данных в одном запросе. Я рад за решение, включающее любую комбинацию соединений или подзапросов по мере необходимости. Спасибо.

Answer 1

Вам просто нужно использовать order by для таблицы синхронизации в вашем запросе, а затем group by, так как mysql не поддерживает order by before group by, потому что mysql использует first group by order by.

Так что вы можете использовать ниже запрос.

SELECT d.data_id, d.name, d.date_created, s.sync_id, s.result, s.details
FROM `data` AS d LEFT JOIN (
  SELECT sync_id, data_id, result, details, date_created FROM (
    SELECT sync_id, data_id, result, details, date_created
    FROM sync
    ORDER BY date_created DESC
  ) a GROUP BY a.data_id
) s
ON d.data_id = s.data_id 
ORDER BY d.date_created DESC;

Answer 2

Есть несколько способов сделать это. Вот тот, который объединяет sync стол, чтобы получить последнюю date_created для каждого data_id запись:

SELECT d.data_id, d.name, d.date_created, s.result, s.details 
FROM data AS d LEFT JOIN
     sync AS s
     ON d.data_id = s.data_id LEFT JOIN
     (select s.data_id, max(date_created) as maxdc
      from sync s
      group by s.data_id
     ) smax
     on s.data_id = smax.data_id and s.date_created = smax.maxdc
WHERE s.result = 0 OR s.result IS NULL;