Групповой максимум в большем запросе

Question

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

У меня две таблицы application а также emailодно приложение может иметь много писем. Что я пытаюсь сделать в своем запросе, так это получить все детали из приложения и присоединиться к таблице электронной почты (на самом деле я получаю только внешний ключ из электронной почты для другой таблицы, которая указывает, был ли получен ответ на письмо), получая последний электронная почта отправлена ​​на основе max(timestamp), поэтому я пытаюсь использовать групповой максимум.

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

SELECT  `application` . * ,  `email1`.`student_email_id` AS  `email_student_email_id` 
FROM  `application` 
LEFT JOIN (
  SELECT MAX( tstamp ) AS tstamp, id, student_email_id, application_id
  FROM email
  GROUP BY id, student_email_id, application_id
) AS email1 ON  `email1`.`application_id` =  `application`.`id` 
WHERE  `application`.`status` =  'returned'

Это то, что поначалу работало, но теперь вызывает проблемы, и я уверен, что это довольно небрежный код:

select `application`.*, `email1`.`student_email_id` as `email_student_email_id`
from `application` 
left join (
  select student_email_id, max(tstamp) as tstamp, application_id
  from email 
  group by application_id, tstamp
  order by tstamp desc
  limit 1) as email1 on `email1`.`application_id` = `application`.`id` 
where `application`.`status` = 'returned'

Любое руководство будет высоко ценится, если вам нужно увидеть больше кода, пожалуйста, спросите! Благодарю.

Дальнейшая ясность, если это необходимо для настройки моей БД и того, что должно происходить (оставив неважные детали):

Application Table
+----+----------+
| id |  status  |
+----+----------+
|  1 | returned |
+----+----------+

Email Table
+----+------------+----------------+------------------+
| id |   tstamp   | application_id | student_email_id |
+----+------------+----------------+------------------+
|  1 | 2014-12-26 |              1 | NULL             |
|  2 | 2014-12-27 |              1 | 3                |
+----+------------+----------------+------------------+

Запрос должен показывать следующее:

+----+----------+------------------------+
| id |  status  | email_student_email_id |
+----+----------+------------------------+
|  1 | returned |                      3 |
+----+----------+------------------------+

Первое решение выше показывает дубликаты всего (возможно, я почти там), а второе показывает нулевое значение для столбцов объединенной таблицы, хотя я уверен, что он работал на одном этапе или, по крайней мере, в изоляции!

Answer 1

Вы ищете последний ряд в вашем Email таблица для каждого отдельного application_id,

Ваш подзапрос, чтобы получить это не совсем правильно. Вот как вы это получаете.

SELECT s.application_id, e.student_email_id
  FROM email e
  JOIN (
         SELECT MAX(tstamp) tstamp, application_id
           FROM email
          GROUP BY application_id
       ) s ON e.application_id = s.application_id AND e.tstamp = s.tstamp

Есть другой способ сделать это, который может быть более эффективным. Это будет работать, если id столбец является автоинкрементным столбцом.

SELECT s.application_id, e.student_email_id
  FROM email e
  JOIN (
         SELECT MAX(id) id
           FROM email
          GROUP BY application_id
       ) s ON e.id = s.id

Любой из этих предыдущих подзапросов получает самый последний идентификатор student_email_id для каждого идентификатора приложения. Второй использует JOIN, чтобы извлечь только самый высокий номер идентификатора для каждого application_id, и использует этот идентификатор, чтобы найти самый последний student_email_id.

Ваш подзапрос был этим. Он не получает то, на что вы надеялись.

 SELECT MAX( tstamp ) AS tstamp, id, student_email_id, application_id /*wrong*/
   FROM email
  GROUP BY id, student_email_id, application_id 

Вы сгруппировали это по идентификатору. Это означает, что вы получите все подробные строки. Это не то, что вы хотите. Даже это

 SELECT MAX( tstamp ) AS tstamp, student_email_id, application_id  /*wrong*/
   FROM email
  GROUP BY student_email_id, application_id 

даст вам более одной записи для каждого значения application_id.

Итак, запрос, который вам нужен:

SELECT  application.* ,  email1.student_email_id AS  email_student_email_id 
  FROM  application 
  LEFT JOIN (
              SELECT s.application_id, e.student_email_id
                FROM email e  
                JOIN (
                       SELECT MAX(id) id
                         FROM email
                        GROUP BY application_id
                     ) s ON e.id = s.id
           ) AS email1 ON  email1.application_id =  application.id 
 WHERE application.status =  'returned'

Когда вы разрабатываете подобные запросы, разумно тестировать изнутри, начиная с самого внутреннего подзапроса.