MySQL "Group By" и " Order By"

Question

MySQL "Group By" и " Order By"

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

SELECT 

    `timestamp`, `fromEmail`, `subject`

FROM `incomingEmails` 

GROUP BY LOWER(`fromEmail`) 

ORDER BY `timestamp` DESC

запрос почти работает так, как я хочу - он выбирает записи, сгруппированные по электронной почте. Проблема в том, что тема и метка времени не соответствуют самой последней записи для конкретного адреса электронной почты.

например, он может вернуть:

fromEmail: [email protected], subject: hello

fromEmail: [email protected], subject: welcome

когда записи в базе данных являются:

fromEmail: [email protected], subject: hello

fromEmail: [email protected], subject: programming question

fromEmail: [email protected], subject: welcome

Если тема" вопрос программирования " является самой последней, как я могу заставить MySQL выбрать эту запись при группировке электронных писем?

893 6

mysql group-by sql sql-order-by

6 ответов:

Comments

Ничего не найдено.

b7kich · Accepted Answer · 2017-08-24 22:46:25

простое решение состоит в том, чтобы обернуть запрос в подвыборку с помощью оператора ORDER первый и применение группы по позже:
SELECT * FROM ( 
    SELECT `timestamp`, `fromEmail`, `subject`
    FROM `incomingEmails` 
    ORDER BY `timestamp` DESC
) AS tmp_table GROUP BY LOWER(`fromEmail`)
Это похоже на использование соединения, но выглядит намного лучше.

используя неагрегатная столбцы в Выберите с группой пунктом является нестандартным. MySQL обычно возвращает значения первой строки, которую он находит, и отбрасывает остальные. Любые предложения ORDER BY будут применяться только к возвращаемому значению столбца, не бракованных.

ВАЖНОЕ ОБНОВЛЕНИЕ Выбрав неагрегатная столбцов, используемых для работы в практике, но не следует полагаться. За документация MySQL " это полезно в первую очередь, когда все значения в каждом неагрегированном столбце, не названном в группе BY, одинаковы для каждой группы. Сервер-это свободно выбирать любое значение из каждой группы, так если они не совпадают, выбранные значения неопределенны."

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

по состоянию на 5.7.5 ONLY_FULL_GROUP_BY включен по умолчанию, т. е. невозможно использовать неагрегатные столбцы.

посмотреть https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

Andomar · Accepted Answer · 2009-07-01 02:10:08

вот один подход:
SELECT cur.textID, cur.fromEmail, cur.subject, 
     cur.timestamp, cur.read
FROM incomingEmails cur
LEFT JOIN incomingEmails next
    on cur.fromEmail = next.fromEmail
    and cur.timestamp < next.timestamp
WHERE next.timestamp is null
and cur.toUserID = '$userID' 
ORDER BY LOWER(cur.fromEmail)
в основном, вы присоединяетесь к таблице на себя, ища более поздние строки. В предложении where вы заявляете, что более поздних строк не может быть. Это дает вам только последнюю строку.

Если может быть несколько писем с одной и той же меткой времени, этот запрос нуждается в уточнении. Если в таблице электронной почты есть инкрементный столбец ID, измените соединение следующим образом:
LEFT JOIN incomingEmails next
    on cur.fromEmail = next.fromEmail
    and cur.id < next.id

11101101b · Accepted Answer · 2013-04-30 22:53:32

сделайте группу после заказа, обернув свой запрос с помощью группы следующим образом:
SELECT t.* FROM (SELECT * FROM table ORDER BY time DESC) t GROUP BY t.from

noonex · Accepted Answer · 2014-02-06 16:03:41

в соответствии со стандартом SQL вы не можете использовать неагрегатные столбцы в списке выбора. MySQL позволяет такое использование (используется режим uless ONLY_FULL_GROUP_BY), но результат не предсказуем.

ONLY_FULL_GROUP_BY

вы должны сначала выбрать fromEmail, MIN (read), а затем, со вторым запросом (или подзапросом) - Subject.

Marcus · Accepted Answer · 2016-02-27 14:15:19

как уже указывалось в ответе, текущий ответ неверен, потому что группа произвольно выбирает запись из окна.

Если вы используете MySQL 5.6, или MySQL 5.7 с ONLY_FULL_GROUP_BY правильный (детерминированные) запрос:
SELECT incomingEmails.*
  FROM (
    SELECT fromEmail, MAX(timestamp) `timestamp`
    FROM incomingEmails
    GROUP BY fromEmail
  ) filtered_incomingEmails
  JOIN incomingEmails USING (fromEmail, timestamp)
GROUP BY fromEmail, timestamp
для того, чтобы запрос, чтобы эффективно работать, необходимо правильное индексирование.

обратите внимание, что для упрощения целей, я удалил LOWER(), который в большинстве случаев не будет использоваться.

Mike N · Accepted Answer · 2012-10-31 16:00:56

Я боролся с обоими этими подходами для более сложных запросов, чем показанные, потому что подход подзапроса был ужасно неэффективен независимо от того, какие индексы я поставил, и потому что я не мог получить внешнее самосоединение через Hibernate

лучший (и самый простой) способ сделать это-сгруппировать что-то, что построено, чтобы содержать конкатенацию требуемых полей, а затем вытащить их с помощью выражений в предложении SELECT. Если вам нужно сделать Макс () сделать убедитесь, что поле, которое вы хотите MAX() over, всегда находится в самом значительном конце объединенного объекта.
http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html
Если вы можете получить am insert/update event (например, триггер) для предварительного вычисления конкатенации полей, вы можете индексировать его, и запрос будет таким же быстрым, как если бы группа была только полем, которое вы действительно хотели MAX (). Вы даже можете использовать его, чтобы получить максимум из нескольких полей. Я использую его для выполнения запросов к многомерным деревьям, выраженным как вложенные наборы.