Sql oracle как выбрать строку с максимальным значением
Обновлено: 05.07.2024
У меня есть эта таблица для документов (упрощенная версия здесь):
Как выбрать одну строку на один идентификатор и только наибольший оборот?
С приведенными выше данными результат должен содержать две строки: [1, 3, . ] и [2, 1, ..] . Я использую MySQL.
В настоящее время я использую проверки в цикле while для обнаружения и перезаписи старых revs из набора результатов. Но является ли это единственным методом достижения результата? Разве нет решения SQL?
Обновление
Как показывают ответы, есть SQL-решение, а здесь демо-версия sqlfiddle.
ОТВЕТЫ
Ответ 1
На первый взгляд.
Все, что вам нужно, это предложение GROUP BY с функцией агрегации MAX :
Это так просто, не так ли?
Я просто заметил, что вам нужен столбец content .
Это очень распространенный вопрос в SQL: найдите все данные для строки с некоторым максимальным значением в столбце для каждого идентификатора группы. Я много слышал о своей карьере. Фактически, это был один из вопросов, которые я ответил в своем текущем техническом интервью.
В принципе, у вас есть два подхода к решению этой проблемы:
Соединение с простым group-identifier, max-value-in-group Подзапросом
В этом подходе вы сначала найдете group-identifier, max-value-in-group (уже разрешенный выше) в подзапросе. Затем вы присоединяете свою таблицу к подзапросу с равенством как на group-identifier , так и на max-value-in-group :
Левое соединение с помощью себя, настройка условий соединения и фильтров
В этом подходе вы оставили соединение с самим собой. Равенство, конечно же, идет в group-identifier . Затем два умных перемещения:
- Второе условие объединения имеет левое боковое значение меньше правого значения
- Когда вы выполните шаг 1, строки (ы), которые на самом деле имеют максимальное значение, будут иметь NULL в правой части (это a LEFT JOIN , помните?). Затем мы фильтруем объединенный результат, показывая только строки, где правая сторона NULL .
Итак, вы закончите:
Заключение
Оба подхода приносят точный результат.
Если у вас есть две строки с max-value-in-group для group-identifier , обе строки будут в результате в обоих подходах.
Оба подхода совместимы с SQL ANSI, поэтому они будут работать с вашей любимой РСУБД, независимо от ее "вкуса".
Оба подхода также совместимы с производительностью, однако ваш пробег может варьироваться (РСУБД, структура БД, индексы и т.д.). Поэтому, когда вы выбираете один подход по сравнению с другим, ориентир. И убедитесь, что вы выбрали тот, который имеет для вас большой смысл.
Ответ 2
Мое предпочтение заключается в том, чтобы использовать как можно меньше кода.
Вы можете сделать это, используя IN попробуйте следующее:
на мой взгляд, он менее сложный. легче читать и поддерживать.
Ответ 3
Еще одно решение - использовать коррелированный подзапрос:
Наличие индекса (id, rev) делает подзапрос почти как простой поиск.
Ниже приведены сравнения с решениями в ответе @AdrianCarneiro (subquery, leftjoin), основанные на измерениях MySQL с таблицей InnoDB размером
1 миллион записей, размер группы: 1-3.
В то время как для полного сканирования таблицы подзапросы/левые/коррелированные тайминги относятся друг к другу как 6/8/9, когда дело доходит до прямого поиска или партии ( id in (1,2,3) ), подзапрос выполняется намного медленнее, чем остальные (из-за повторной передачи подзапрос). Однако я не мог отличать ледяные и коррелированные решения в скорости.
Наконец, поскольку leftjoin создает n * (n + 1)/2, объединяется в группы, его производительность может сильно зависеть от размера групп.
Ответ 4
Добавлен в стандарт SQL Стандарт ANSI/ISO SQL: 2003 и более поздние версии с ANSI/ISO Standard SQL: 2008, теперь доступны функции окна (или окна) со всеми основными поставщиками. Существует больше типов ранговых функций, доступных для решения проблемы связи: RANK, DENSE_RANK, PERSENT_RANK .
Ответ 5
Я не могу ручаться за производительность, но вот трюк, вдохновленный ограничениями Microsoft Excel. Он имеет некоторые хорошие функции.
GOOD STUFF
- Он должен принудительно вернуть только одну "максимальную запись", даже если есть галстук (иногда полезный)
- Он не требует соединения
ПОДХОД
Это немного уродливо и требует, чтобы вы знали что-то о диапазоне допустимых значений столбца rev. Предположим, что мы знаем, что столбец rev - это число от 0,00 до 999, включая десятичные числа, но что только две цифры справа от десятичной точки (например, 34.17 будет действительным значением).
Суть в том, что вы создаете единый синтетический столбец путем конкатенации/упаковки первичного поля сравнения вместе с данными, которые вы хотите. Таким образом, вы можете заставить агрегированную функцию SQL MAX() возвращать все данные (поскольку она была упакована в один столбец). Затем вам нужно распаковать данные.
Вот как он выглядит с приведенным выше примером, написанным на SQL
Упаковка начинается с того, что для того, чтобы столбец rev был числом известных символов, независимо от значения rev, чтобы, например,
- 3.2 становится 1003.201
- 57 становится 1057.001
- 923.88 становится 1923.881
Если вы это сделаете правильно, сравнение строк двух чисел должно давать то же самое "max", что и числовое сравнение двух чисел, и легко преобразовать обратно к исходному номеру с помощью функции подстроки (которая доступна в одной форме или другой почти везде).
Ответ 6
Я думаю, что это самое простое решение:
- SELECT * : вернуть все поля.
- FROM Employee : Таблица искалась.
- (SELECT *. ) подзапрос: Вернуть всех людей, отсортированных по зарплате.
- GROUP BY employeesub.Salary : Принудительный возврат строки зарплаты каждого сотрудника.
Если вам нужен только один ряд, это еще проще:
Я также считаю, что проще всего разбить, понять и изменить для других целей:
- ORDER BY Employee.Salary DESC : ORDER BY Employee.Salary DESC результаты по зарплате, сначала с самой высокой зарплатой.
- LIMIT 1 : вернуть только один результат.
Ответ 7
Что-то вроде этого?
Ответ 8
Поскольку это самый популярный вопрос, связанный с этой проблемой, я еще раз отвечу на него здесь:
Похоже, есть более простой способ сделать это (но только в MySQL):
Пожалуйста, отметьте ответ пользователя Bohemian в этом вопросе за краткий и элегантный ответ на эту проблему.
Редактировать: хотя это решение работает для многих людей, оно может быть нестабильным в долгосрочной перспективе, поскольку MySQL не гарантирует, что оператор GROUP BY будет возвращать значимые значения для столбцов, не входящих в список GROUP BY. Так что используйте это решение на свой страх и риск!
Ответ 9
Мне нравится использовать решение NOT EXIST EXIST -based для этой проблемы:
Это выберет все записи с максимальным значением в группе и позволит вам выбрать другие столбцы.
Ответ 10
НЕ mySQL, но для других людей, которые находят этот вопрос и используют SQL, другим способом решения проблемы greatest-n-per-group является использование Cross Apply в MS SQL
Вот пример в SqlFiddle
Ответ 11
Третье решение, о котором я почти никогда не упоминал, имеет специфику MySQL и выглядит следующим образом:
Да, это выглядит ужасно (преобразование в строку и обратно и т.д.), но по моему опыту это обычно быстрее, чем другие решения. Возможно, это только для моих случаев использования, но я использовал его на таблицах с миллионами записей и множеством уникальных идентификаторов. Может быть, потому, что MySQL довольно плохо оптимизирует другие решения (по крайней мере, в 5,0 дней, когда я придумал это решение).
Важно то, что GROUP_CONCAT имеет максимальную длину для строки, которую он может создать. Вероятно, вы хотите повысить этот предел, установив переменную group_concat_max_len . И имейте в виду, что это будет ограничение на масштабирование, если у вас есть большое количество строк.
В любом случае вышеупомянутое не работает напрямую, если ваше поле содержимого уже является текстом. В этом случае вы, вероятно, захотите использовать другой разделитель, например, \0. Вы также быстрее столкнетесь с префиксом group_concat_max_len .
Ответ 12
Если у вас много полей в инструкции select, и вы хотите получить последнее значение для всех этих полей с помощью оптимизированного кода:
Ответ 13
Я думаю, ты этого хочешь?
Ответ 14
Как насчет этого:
Ответ 15
Ответ 16
Я бы использовал это:
Подзапрос SELECT не слишком эффективен, но в разделе JOIN кажется полезным. Я не эксперт в оптимизации запросов, но я пробовал в MySQL, PostgreSQL, FireBird и работает очень хорошо.
Вы можете использовать эту схему в нескольких соединениях и с предложением WHERE. Это мой рабочий пример (решение идентично вашей проблеме с таблицей "твердое" ):
Его спрашивают на таблицах с подростками таких записей, и он занимает менее 0,01 секунды на действительно не слишком сильной машине.
Я бы не использовал предложение IN (как упоминается выше). IN предоставляется для использования с короткими списками констант, а не как фильтр запросов, построенный на подзапросе. Это связано с тем, что подзапрос в IN выполняется для каждой отсканированной записи, которая может сделать запрос очень медленным.
Ответ 17
Ответ 18
Другой способ выполнить работу - использовать аналитическую функцию MAX() в предложении OVER PARTITION.
Другое ROW_NUMBER() OVER PARTITION, уже задокументированное в этом посте,
Эти 2 SELECT хорошо работают на Oracle 10g.
Решение MAX(), безусловно, ROW_NUMBER() решение ROW_NUMBER() поскольку сложность MAX() равна O(n) а сложность ROW_NUMBER() - как минимум O(n.log(n)) где n представляет количество записей в таблице!
Ответ 19
Вот хороший способ сделать это
Используйте следующий код:
Ответ 20
Мне нравится делать это путем ранжирования записей в каком-то столбце. В этом случае значения ранга rev сгруппированы по id . Те, у кого выше rev , будут иметь более низкий рейтинг. Таким образом, наивысший rev будет иметь рейтинг 1.
Не уверен, что введение переменных делает все это медленнее. Но, по крайней мере, я не дважды запрашиваю YOURTABLE .
Ответ 21
Отсортировано поле rev в обратном порядке, а затем сгруппировано по id, которое дало первую строку каждой группы, которая является самой высокой величиной rev.
Это дало следующий результат в MySql 5.5 и 5.6
Ответ 22
вот еще одно решение, которое поможет кому-то
Ответ 23
Это то, что сработало для меня.
Ответ 24
Здесь другое решение для получения записей только с полем, которое имеет максимальное значение для этого поля. Это работает для SQL400, на котором я работаю. В этом примере записи с максимальным значением в поле FIELD5 будут получены следующим оператором SQL.
Ответ 25
Если кто-то ищет Linq verson, это, похоже, работает для меня:
Ответ 26
Многие, если не все, другие ответы здесь подходят для небольших наборов данных. Для масштабирования требуется больше внимания. См. здесь.
В нем обсуждается несколько более быстрых способов сделать groupwise max и top-N для каждой группы.
Ответ 27
Я использовал ниже, чтобы решить свою проблему. Сначала я создал временную таблицу и вставил максимальное значение rev на уникальный идентификатор.
Ответ 28
Вы можете сделать выбор без объединения, когда вы объединяете rev и id в одно значение maxRevId для MAX() а затем разделяете его на исходные значения:
Это особенно быстро, когда есть сложное соединение вместо одной таблицы. При традиционных подходах комплексное соединение будет выполнено дважды.
Приведенная выше комбинация проста с битовыми функциями, когда rev и id имеют значение INT UNSIGNED (32 бита), а объединенное значение соответствует значению BIGINT UNSIGNED (64 бита). Когда id & rev больше 32-битных значений или состоят из нескольких столбцов, необходимо объединить значение, например, в двоичное значение с подходящим заполнением для MAX() .
Ответ 29
Это не чистый SQL. Это будет использовать SQLAlchemy ORM.
Я пришел сюда в поисках справки по SQLAlchemy, поэтому я продублирую ответ Адриана Карнейру с версией python/SQLAlchemy, в частности, с внешней частью соединения.
Этот запрос отвечает на вопрос:
"Можете ли вы вернуть мне записи в этой группе записей (на основе одного и того же идентификатора), которые имеют наибольший номер версии".
Это позволяет мне дублировать запись, обновлять ее, увеличивать ее номер версии и получать копию старой версии таким образом, чтобы я мог показывать изменения во времени.
Я хочу получить UserId, значение для max (Date) для каждого UserId. То есть значение для каждого идентификатора пользователя, который имеет самую последнюю дату. Есть ли способ сделать это просто в SQL? (Желательно Oracle)
Обновление: извинения за любую двусмысленность: мне нужно получить ВСЕ UserIds. Но для каждого UserId только та строка, где у этого пользователя самая последняя дата.
Что если несколько строк имеют максимальное значение даты для определенного идентификатора пользователя?Это извлечет все строки, для которых значение столбца my_date равно максимальному значению my_date для этого идентификатора пользователя. Это может извлечь несколько строк для идентификатора пользователя, где максимальная дата находится в нескольких строках.
«Аналитические функции рока»
Редактировать: Что касается первого комментария .
«Использование аналитических запросов и самостоятельное объединение наносят ущерб цели аналитических запросов»
В этом коде нет самостоятельного объединения. Вместо этого на результат встроенного представления помещается предикат, который содержит аналитическую функцию - совершенно другой вопрос и совершенно стандартная практика.
«Окно по умолчанию в Oracle - от первой строки в разделе до текущей»
Оконное предложение применимо только при наличии предложения order by. Без указания по порядку, по умолчанию не применяется ни одно оконное предложение, и ни одно из них не может быть указано явно.
Применительно к таблице, имеющей 8,8 миллиона строк, этот запрос занимал половину времени запросов в некоторых других высоко голосуемых ответах. Кто-нибудь хочет опубликовать ссылку на MySQL эквивалент этого, если таковой имеется? Не может ли это вернуть дубликаты? Например. если две строки имеют одинаковый user_id и одну и ту же дату (что является максимальным). Вместо этого MAX(. ) OVER (. ) вы также можете использовать ROW_NUMBER() OVER (. ) (для лучших n-на-группу) или RANK() OVER (. ) (для наибольшего-n-на-группу).Я вижу, что многие люди используют для этого подзапросы или другие специфичные для поставщика функции, но я часто делаю этот тип запросов без подзапросов следующим образом. Он использует простой стандартный SQL, поэтому он должен работать в любой марке СУБД.
Другими словами: получить строку, из t1 которой не существует другой строки с такой же UserId и большей датой.
(Я поместил идентификатор «Дата» в разделители, потому что это зарезервированное слово SQL.)
В случае, если t1."Date" = t2."Date" удвоение появляется. Обычно таблицы имеют auto_inc(seq) ключ, например id . Во избежание дублирования можно использовать следующее:
Re комментарий от @Farhan:
Вот более подробное объяснение:
Внешнее соединение пытается соединиться t1 с t2 . По умолчанию все результаты t1 возвращаются, и, если есть совпадение t2 , оно также возвращается. Если t2 для данной строки не найдено совпадений t1 , тогда запрос по-прежнему возвращает строку t1 и использует NULL в качестве заполнителя для всех t2 столбцов. Именно так работают внешние соединения.
Хитрость в этом запросе состоит в том, чтобы спроектировать условие сопоставления соединения таким образом, t2 чтобы оно совпадало с тем же userid , а то и больше date . Идея состоит в том, что если строка существует в t2 этом, имеет большее значение date , то строка в t1 сравнении с ней не может быть лучшей date для этого userid . Но если нет совпадений, т. Е. Если в строке нет строки, t2 значение которой больше, date чем в строке, t1 мы знаем, что строка t1 была строкой с наибольшим значением date для данного userid .
Ух ты, Билл. Это самое креативное решение этой проблемы, которое я видел. Это довольно производительно на моем довольно большом наборе данных. Это наверняка превосходит многие другие решения, которые я видел, или мои собственные попытки решить эту проблему. Применительно к таблице с 8,8 миллионами строк этот запрос занимал почти вдвое больше времени, чем в принятом ответе. @Derek: Оптимизация зависит от бренда и версии СУБД, а также от наличия соответствующих индексов, типов данных и т. Д. В MySQL этот тип запроса, по-видимому, фактически заставляет его зацикливаться на результате декартового объединения таблиц, что приводит к времени O (n ^ 2). Использование метода подзапроса вместо этого уменьшило время запроса с 2,0 до 0,003 с. YMMV. Есть ли способ адаптировать это для соответствия строк, где дата наибольшая дата меньше или равна заданной пользователем дате? Например, если пользователь указывает дату «23-ОКТ-2011», а таблица содержит строки для «24-ОКТ-2011», «22-ОКТ-2011», «20-ОКТ-2011», то я хочу получить "22-ОКТ-2011". Я почесал голову и некоторое время читал этот фрагмент . В моих тестах с использованием таблицы с большим числом строк это решение занимало примерно вдвое больше времени, чем в принятом ответе. Я подтверждаю, что это намного быстрее, чем другие решения @ user2067753 Нет, полная запись не возвращается. Вы можете использовать одно и то же выражение MAX () .. KEEP .. для нескольких столбцов, поэтому вы можете выбрать все нужные столбцы. Но это неудобно, если вы хотите большое количество столбцов и предпочитаете использовать SELECT *.Я не знаю ваших точных имен столбцов, но это будет что-то вроде этого:
Вы, вероятно, недооцениваете оптимизатор запросов Oracle. Не за что. Это почти наверняка будет реализовано как полное сканирование с вложенным циклом, чтобы получить даты. Вы говорите о логических операциях ввода-вывода в 4 раза превышающем количество строк в таблице, и вам страшно за нетривиальные объемы данных. К вашему сведению, «Не эффективно, но работает» - это то же самое, что «Работает, но не эффективно». Когда мы отказались от эффективной цели дизайна? +1, потому что, когда ваши таблицы данных не являются миллионами строк в длину, это самое понятное решение. когда у вас есть несколько разработчиков всех уровней квалификации, которые изменяют код, понятность важнее доли секунды, что незаметно.Не будучи на работе, у меня нет Oracle для передачи, но я, кажется, напоминаю, что Oracle позволяет сопоставлять несколько столбцов в предложении IN, что должно, по крайней мере, избегать опций, использующих коррелированный подзапрос, что редко является хорошим идея.
Возможно, что-то вроде этого (не помню, нужно ли заключать в скобки список столбцов):
РЕДАКТИРОВАТЬ: Просто попробовал это по-настоящему:
Так что это работает, хотя некоторые из новых вещей, упомянутых в другом месте, могут быть более производительными.
Это хорошо работает и на PostgreSQL. И мне нравится его простота и универсальность - подзапрос говорит: «Вот мои критерии», внешний запрос - «И вот детали, которые я хочу увидеть». +1.Я знаю, что вы просили Oracle, но в SQL 2005 мы теперь используем это:
У меня нет Oracle для его тестирования, но самое эффективное решение - использовать аналитические запросы. Это должно выглядеть примерно так:
Я подозреваю, что вы можете избавиться от внешнего запроса и поставить разные на внутренний, но я не уверен. В то же время я знаю, что это работает.
Под капотом аналитические запросы сортируют весь набор данных, а затем обрабатывают его последовательно. По мере обработки вы разбиваете набор данных в соответствии с определенными критериями, а затем для каждой строки просматриваете какое-то окно (по умолчанию первое значение в разделе соответствует текущей строке - это значение по умолчанию также является наиболее эффективным) и может вычислять значения, используя количество аналитических функций (список которых очень похож на агрегатные функции).
В этом случае вот что делает внутренний запрос. Весь набор данных сортируется по UserId, а затем по дате DESC. Затем он обрабатывает его за один проход. Для каждой строки вы возвращаете UserId и первую Date, увиденную для этого UserId (поскольку даты отсортированы DESC, это максимальная дата). Это дает вам ваш ответ с дублированными строками. Тогда внешний DISTINCT сдавливает дубликаты.
Это не особенно впечатляющий пример аналитических запросов. Для гораздо большего выигрыша рассмотрите возможность получения таблицы финансовых квитанций и расчета для каждого пользователя и квитанции, промежуточной суммы того, что они заплатили. Аналитические запросы решают это эффективно. Другие решения менее эффективны. Именно поэтому они являются частью стандарта SQL 2003 года. (К сожалению, у Postgres их пока нет. Гррр . )
Обновление: Извинения за любую двусмысленность: мне нужно получить ВСЕ пользовательские идентификаторы. Но для каждого UserId используется только эта строка, в которой этот пользователь имеет самую последнюю дату.
Это приведет к извлечению всех строк, для которых значение столбца my_date равно максимальному значению my_date для этого идентификатора пользователя. Это может извлечь несколько строк для идентификатора пользователя, где максимальная дата указана в нескольких строках.
"Аналитические функции рок"
Изменить: Что касается первого комментария.
", используя аналитические запросы и самосоединение, поражает цель аналитических запросов
В этом коде нет самостоятельного объединения. Вместо этого существует предикат, помещенный на результат встроенного представления, который содержит аналитическую функцию - совсем другое дело и полностью стандартную практику.
"Окно по умолчанию в Oracle относится к первой строке раздела к текущему"
Предложение windowing применимо только при наличии предложения order by. Без предложения order by по умолчанию не применяется предложение windowing, и никто не может быть явно указан.
Я вижу, что многие люди используют подзапросы или другие специфические для вендора функции, но я часто делаю такой запрос без подзапросов следующим образом. Он использует простой стандартный SQL, поэтому он должен работать в любом бренде СУБД.
Другими словами: выберите строку из t1, где не существует другой строки с тем же UserId и большей датой.
(я помещаю идентификатор "Дата" в разделители, потому что это зарезервированное слово SQL.)
В случае, если t1."Date" = t2."Date" , появляется удвоение. Обычно таблицы имеют auto_inc(seq) ключ, например. id . Во избежание удвоения можно использовать следующее:
Re comment от @Farhan:
Вот более подробное объяснение:
Внешнее соединение пытается соединить t1 с t2. По умолчанию возвращаются все результаты t1, и если есть совпадение в t2, оно также возвращается. Если в t2 для данной строки t1 нет совпадения, запрос по-прежнему возвращает строку t1 и использует NULL в качестве заполнителя для всех столбцов t2. Это то, как работают внешние соединения.
Трюк в этом запросе заключается в разработке условия согласования соединения, так что t2 должен соответствовать одному и тому же идентификатору пользователя и более высокой дате. Идея заключается в том, что строка существует в t2 с более высокой датой, тогда строка в t1, которую она сравнивает, не может быть наибольшей датой для этого пользователя. Но если совпадения нет - т.е. Если в t2 не существует строки с большей датой, чем строка в t1 - мы знаем, что строка в t1 была строкой с наибольшей датой для данного идентификатора пользователя.
Я не знаю ваших точных имен столбцов, но это было бы примерно так:
Не работая, у меня нет Oracle, но я, похоже, помню, что Oracle позволяет сопоставлять несколько столбцов в предложении IN, что должно, по крайней мере, избегать опций, которые используют коррелированный подзапрос, который редко хорошая идея.
Что-то вроде этого, возможно (не помню, должен ли список столбцов быть заключен в скобки или нет):
EDIT: просто попробовал это для реального:
Таким образом, это работает, хотя некоторые из новых причудливых вещей, упомянутых в других местах, могут быть более эффективными.
Я знаю, что вы попросили Oracle, но в SQL 2005 мы теперь используем это:
У меня нет Oracle для тестирования, но наиболее эффективным решением является использование аналитических запросов. Он должен выглядеть примерно так:
Я подозреваю, что вы можете избавиться от внешнего запроса и наделить его внутренним, но я не уверен. Тем временем я знаю, что это работает.
Под капотом аналитические запросы сортируют весь набор данных, затем обрабатывают его последовательно. По мере его обработки вы разбиваете набор данных в соответствии с определенными критериями, а затем для каждой строки просматривается какое-то окно (по умолчанию используется первое значение раздела в текущей строке - это значение по умолчанию также является наиболее эффективным) и может вычислять значения с помощью количество аналитических функций (список которых очень похож на совокупные функции).
В этом случае вот что делает внутренний запрос. Весь набор данных сортируется по UserId, а затем DES DES. Затем он обрабатывает его за один проход. Для каждой строки вы возвращаете UserId и первую Date, указанную для этого UserId (поскольку даты сортируются DESC, это максимальная дата). Это дает вам ответ с повторяющимися строками. Затем внешний DISTINCT выдает дубликаты.
Это не особенно впечатляющий пример аналитических запросов. Для гораздо большей победы подумайте о принятии таблицы финансовых поступлений и расчета для каждого пользователя и квитанции, общая сумма того, что они заплатили. Аналитические запросы эффективно решаются. Другие решения менее эффективны. Именно поэтому они являются частью стандарта SQL 2003. (К сожалению, у Postgres их еще нет. Grrr. )
web программирование, администрирование и всякая всячина, которая может оказаться полезной
Выбираем записи с максимальным значением определенного поля.
На всякий случай - требуется получить не сами максимальные значения, а записи, в которых определенное поле равно максимальному.
Если поля всего два - сработает простой запрос:
id|date
select id, max(date) from table1;
При добавлении еще одного поля задача выбора записей с максимальным значением усложняется:
чтобы получить все записи с максимальной датой:
1) для MySQL версии 4.1 и выше можно сделать одним запросом:
select * from table1 where date in (select max(date) from table1)
2)Если версия ниже, то это надо делать в два запроса:
select @mxdate:=max(date) from table1;
select * from table1 where date=@mxdate;
id|date|count (id, date primary key) - next level
нужно вычислить count для каждого id с последней датой (с максимальным значением даты)
решение в один запрос рабочее, но нерациональное - на склейку и сравнение строк уходит неоправданно много времени
SELECT * FROM cms_catalog_goods_is_arc
WHERE CONCAT( date, id )
IN (SELECT concat( max( date ) , id ) FROM cms_catalog_goods_is_arc GROUP BY id )
Гораздо быстрей операция поиска строк с максимальным значением производится с использованием временных таблиц:
CREATE Temporary TABLE table2 (
`id` int(11) NOT NULL, `date` datetime NOT NULL, `cnt` int(11) NOT NULL,
PRIMARY KEY (`id`,`date`)
) ;
insert into table2 (id,date) select id, max(date) from table1 group by id;
update table2 t2, table1 t1 set t2.cnt=t1.cnt where t2.id=t1.id and t2.date=t1.date;
теперь в table2 все нужные нам записи.
1)
select *
from (
select t.*, row_number()
over(partition by num
order by dt desc) as rn
from <table_name> t
)
where rn = 1;
2)
select t1.*
from <table_name> t1
left join <table_name> t2
on t1.num = t2.num
and t1.dt < t2.dt
where t2.num is null;
3)
select *
from <table_name> t1
where (num, dt) in
(
select num, max(dt)
from <table_name>
group by num
);
4)
select t1.*
from <table_name> t1,
(
select num, max(dt) as dt
from <table_name>
group by num
) v
where t1.num = v.num
and t1.dt = v.dt;
5)
select t1.*
from <table_name> t1
where not exists(
select null
from <table_name> t0
where t1.num = t0.num
and t1.dt < t0.dt
);
6)
select t1.*
from <table_name> t1
where dt = (
select max(dt)
from <table_name> t0
where t1.num = t0.num
)
Метки: mysql
Автор будет признателен, если Вы поделитесь ссылкой на статью, которая Вам помогла:
BB-код (для вставки на форум)
html-код (для вставки в ЖЖ, WP, blogger и на страницы сайта)
ссылка (для отправки по почте)
Читайте также: