Вопрос по sql, mysql – MySQL «Группировка по» и «Заказ по»

84

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

SELECT 
    `timestamp`, `fromEmail`, `subject`
FROM `incomingEmails` 
GROUP BY LOWER(`fromEmail`) 
ORDER BY `timestamp` DESC

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

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

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 выбрать эту запись при группировке писем?

Ваш Ответ

6   ответов
129

R.first и применяя GROUP BYlater:

SELECT * FROM ( 
    SELECT `timestamp`, `fromEmail`, `subject`
    FROM `incomingEmails` 
    ORDER BY `timestamp` DESC
) AS tmp_table GROUP BY LOWER(`fromEmail`)

Это похоже на использование соединения, но выглядит намного лучше.

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

IMPORTANT UPDATE Выбор неагрегированных столбцов, используемых для практической работы, но на них не следует полагаться. ПоMySQL документация & quot; это полезно, прежде всего, когда все значения в каждом неагрегированном столбце, не названном в GROUP BY, одинаковы для каждой группы. Серверfree to choose any value из каждой группы, такunless they are the same, the values chosen are indeterminate. & Quot;

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

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

Увидеть http://www.cafewebmaster.com/mysql-order-sort-group 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

кажется не работает на 5.7
Я придумал такое же решение несколько лет назад, и это отличное решение. слава к b7kich. Однако здесь есть две проблемы ... GROUP BY нечувствителен к регистру, поэтому LOWER () не нужен, и, во-вторых, $ userID представляется переменной непосредственно из PHP, ваш код может быть уязвим для SQL-инъекций, если $ userID предоставлен пользователем и не принудителен быть целым числом.
ВАЖНОЕ ОБНОВЛЕНИЕ также относится к MariaDB:mariadb.com/kb/en/mariadb/…
Отличная идея, я бы никогда не подумал сделать это таким образом.
Хорошая идея. большое спасибо
2

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

Лучший (и самый простой) способ сделать это - сгруппировать что-либо, сконструированное так, чтобы оно содержало конкатенацию требуемых полей, а затем извлечь их, используя выражения в предложении SELECT. Если вам нужно выполнить MAX (), убедитесь, что поле, над которым вы хотите MAX (), всегда находится на самом значительном конце объединенной сущности.

Ключом к пониманию этого является то, что запрос может иметь смысл только в том случае, если эти другие поля являются инвариантными для любого объекта, который удовлетворяет Max (), поэтому с точки зрения сортировки другие части объединения могут быть проигнорированы. Это объясняет, как сделать это в самом низу этой ссылки.http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

Если вы можете получить событие вставки / обновления am (например, триггер) для предварительного вычисления конкатенации полей, вы можете проиндексировать его, и запрос будет выполняться так же быстро, как если бы сгруппировать было только над полем, которое вы на самом деле хотели MAX ( ). Вы даже можете использовать его, чтобы получить максимум нескольких полей. Я использую его для выполнения запросов к многомерным деревьям, выраженным как вложенные множества.

22

текущий ответ является неправильным, поскольку GROUP BY произвольно выбирает запись из окна.

Если вы используете 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(), который в большинстве случаев не будет использоваться.

Это должен быть правильный ответ. Я только что обнаружил ошибку на своем сайте, связанную с этим.order by в подпункте в других ответах не имеет никакого эффекта вообще.
OMG, пожалуйста, сделайте этот ответ принятым. Принятый потратил впустую 5 часов моего времени :(
29

поместив запрос в GROUP BY следующим образом:

SELECT t.* FROM (SELECT * FROM table ORDER BY time DESC) t GROUP BY t.from
Он выбирает самое новое время, потому что мы заказываемtime DESC и затем группа по получает первый (последний).
Спасибо !stackoverflow.com/a/20460516/25286
Таким образом, GROUP BY` автоматически выбирает последнююtimeили самый новыйtimeили случайно?
Теперь, если бы я только мог делать JOINS на вложенных выборках в VIEWS, в MySQL 5.1. Возможно, эта функция появится в более новой версии.
Спасибо, это отлично сработало для меня на запросе симлара, который я делал.
41

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 вы заявляете, что не может быть более поздних строк. Это дает вам только последний ряд.

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

LEFT JOIN incomingEmails next
    on cur.fromEmail = next.fromEmail
    and cur.id < next.id
Сказал, чтоtextID был неоднозначным = / John Kurlak
При работе с прошлыми и будущими временными метками / датами, чтобы ограничить набор результатов не датами будущего, необходимо добавить еще одно условие кLEFT JOIN критерииAND next.timestamp <= UNIX_TIMESTAMP()
Затем удалите неоднозначность и добавьте в качестве префикса имя таблицы, например, cur.textID. Изменился и в ответе.
Это единственное решение, которое возможно сделать с Doctrine DQL.
Это не работает, когда вы так хорошо пытаетесь самостоятельно объединить несколько столбцов. IE, когда вы пытаетесь найти последнюю электронную почту и последнее имя пользователя, и вам требуется несколько самостоятельных левых соединений для выполнения этой операции в одном запросе.
21

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

ONLY_FULL_GROUP_BY

Сначала вы должны выбрать из электронной почты, MIN (чтение), а затем, с помощью второго запроса (или подзапроса) - Тема.

MIN (чтение) вернул бы минимальное значение «read». Он, вероятно, ищет "прочитать" флаг последней электронной почты вместо.

Похожие вопросы