mysql — SQL выбирает только столбцы с максимальным значением в столбце

У меня есть эта таблица для документов (упрощенная версия здесь):

+------+-------+--------------------------------------+
| id   | rev   | content                              |
+------+-------+--------------------------------------+
| 1    | 1     | ...                                  |
| 2    | 1     | ...                                  |
| 1    | 2     | ...                                  |
| 1    | 3     | ...                                  |
+------+-------+--------------------------------------+

Как выбрать одну строку для каждого идентификатора и только наибольшее число оборотов?
С данными выше, результат должен содержать две строки: [1, 3, ...] а также [2, 1, ..], я использую MySQL.

В настоящее время я использую чеки в while цикл для обнаружения и перезаписи старых оборотов из набора результатов. Но является ли это единственным методом достижения результата? Разве нет SQL решение?

Обновить
Как показывают ответы, есть является решение SQL, и здесь демо sqlfiddle.

Обновление 2
Я заметил после добавления выше sqlfiddle, скорость, с которой вопрос поднимается, превзошла скорость ответов. Это не было намерением! Скрипка основана на ответах, особенно принятых ответах.

+993

Решение

На первый взгляд…

Все, что вам нужно, это GROUP BY оговорка с MAX агрегатная функция:

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

Это никогда не было так просто, правда?

Я просто заметил, что вам нужно content колонна также.

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

На самом деле, это настолько распространено, что сообщество StackOverflow создало один тег, чтобы иметь дело с такими вопросами: .

По сути, у вас есть два подхода к решению этой проблемы:

Присоединение с простым group-identifier, max-value-in-group Суб-запрос

При таком подходе вы сначала находите group-identifier, max-value-in-group (уже решено выше) в подзапросе. Затем вы присоединяете свою таблицу к подзапросу с равенством на обоих group-identifier а также max-value-in-group:

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
SELECT id, MAX(rev) rev
FROM YourTable
GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

Оставлено соединение с самим собой, настройка условий соединения и фильтров

При таком подходе вы оставляете за столом присоединение к себе. Равенство, конечно, идет в group-identifier, Затем 2 умных хода:

  1. Второе условие соединения имеет значение левой стороны меньше правого значения
  2. Когда вы выполните шаг 1, строки, которые на самом деле имеют максимальное значение, будут иметь NULL в правой части (это LEFT JOIN, Помните?). Затем мы фильтруем объединенный результат, показывая только те строки, где правая сторона NULL,

Итак, вы в конечном итоге:

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

Заключение

Оба подхода дают одинаковый результат.

Если у вас есть две строки с max-value-in-group за group-identifierобе строки будут в результате в обоих подходах.

Оба подхода совместимы с SQL ANSI, поэтому будут работать с вашей любимой СУБД, независимо от ее «аромата».

Оба подхода также не влияют на производительность, однако ваш пробег может отличаться (СУБД, структура БД, индексы и т. Д.). Поэтому, когда вы выбираете один подход над другим, эталонный тест. И убедитесь, что вы выбрали тот, который имеет наибольшее значение для вас.

1562

Другие решения

Я предпочитаю использовать как можно меньше кода …

Вы можете сделать это с помощью IN
попробуй это:

SELECT *
FROM t1 WHERE (id,rev) IN
( SELECT id, MAX(rev)
FROM t1
GROUP BY id
)

На мой взгляд, это менее сложно … легче читать и поддерживать.

193

Еще одно решение заключается в использовании коррелированного подзапроса:

select yt.id, yt.rev, yt.contents
from YourTable yt
where rev =
(select max(rev) from YourTable st where yt.id=st.id)

Наличие индекса (id, rev) делает подзапрос почти простым поиском …

Ниже приводятся сравнения с решениями в ответе @ AdrianCarneiro (подзапрос, левое соединение), основанный на измерениях MySQL с таблицей InnoDB ~ 1 миллион записей, размер группы: 1-3.

В то время как для полных сканирований таблицы подзапрос / оставленное соединение / коррелированные моменты времени относятся друг к другу как 6/8/9, когда речь идет о прямом поиске или пакетной обработке (id in (1,2,3)), подзапрос намного медленнее остальных (из-за перезапуска подзапроса). Однако я не мог различить левое соединение и коррелированные решения по скорости.

И последнее замечание: поскольку leftjoin создает n * (n + 1) / 2 объединений в группах, его производительность может сильно зависеть от размера групп …

64

Я поражен, что ни один ответ не предложил решение для оконной функции SQL:

SELECT a.id, a.rev, a.contents
FROM (SELECT id, rev, contents,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
FROM YourTable) a
WHERE a.rank = 1

Добавленные в стандарт SQL ANSI / ISO Standard SQL: 2003 и более поздние, дополненные стандартом ANSI / ISO SQL: 2008, функции окна (или оконного режима) теперь доступны для всех основных поставщиков. Есть еще несколько типов функций ранга, доступных для решения проблемы связи: RANK, DENSE_RANK, PERSENT_RANK,

48

Я не могу ручаться за производительность, но вот трюк, вдохновленный ограничениями Microsoft Excel. У этого есть несколько хороших особенностей

ХОРОШАЯ ВЕЩЬ

  • Это должно вызвать возврат только одной «максимальной записи», даже если есть связь (иногда полезно)
  • Не требует объединения

ПОДХОД

Это немного уродливо и требует, чтобы вы знали кое-что о диапазоне допустимых значений оборот колонка. Давайте предположим, что мы знаем оборот столбец — это число от 0,00 до 999, включая десятичные дроби, но справа от десятичной точки будут только две цифры (например, 34.17 будет допустимым значением).

Суть в том, что вы создаете один синтетический столбец путем объединения строк / упаковки основного поля сравнения вместе с данными, которые вы хотите. Таким образом, вы можете заставить агрегатную функцию MAX () SQL возвращать все данные (потому что они были упакованы в один столбец). Затем вы должны распаковать данные.

Вот как это выглядит на примере выше, написанном на SQL

SELECT id,
CAST(SUBSTRING(max(packed_col) FROM 2 FOR 6) AS float) as max_rev,
SUBSTRING(max(packed_col) FROM 11) AS content_for_max_rev
FROM  (SELECT id,
CAST(1000 + rev + .001 as CHAR) || '---' || CAST(content AS char) AS packed_col
FROM yourtable
)
GROUP BY id

Упаковка начинается с принуждения оборот столбец должен быть числом известной длины символа независимо от значения оборот так что например

  • 3.2 становится 1003.201
  • 57 становится 1057,001
  • 923,88 становится 1923,881

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

41

Я думаю, что это самое простое решение:

SELECT *
FROM
(SELECT *
FROM Employee
ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;
  • SELECT *: вернуть все поля.
  • ОТ сотрудника: Таблица искалась.
  • (ВЫБРАТЬ * …) подзапрос: Вернуть всех людей, отсортированных по зарплате.
  • GROUP BY employeeub.Salary:: принудительно возвращает отсортированный сверху ряд зарплат каждого сотрудника в качестве возвращаемого результата.

Если вам нужен только один ряд, это еще проще:

SELECT *
FROM Employee
ORDER BY Employee.Salary DESC
LIMIT 1

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

  • ЗАКАЗАТЬ Employee.Salary DESC: Упорядочить результаты по зарплате, сначала с самой высокой зарплатой.
  • ПРЕДЕЛ 1: вернуть только один результат.

Понимание этого подхода позволяет решить любую из этих похожих проблем тривиально: получить сотрудника с наименьшей зарплатой (смените DESC на ASC), получить 10 лучших зарабатывающих сотрудников (измените LIMIT 1 на LIMIT 10), отсортировать с помощью другого поля (изменить ORDER BY Employee.Salary to ORDER BY Employee.Commission) и т. Д.

24

Что-то вроде этого?

SELECT yourtable.id, rev, content
FROM yourtable
INNER JOIN (
SELECT id, max(rev) as maxrev FROM yourtable
WHERE yourtable
GROUP BY id
) AS child ON (yourtable.id = child.id) AND (yourtable.rev = maxrev)
16

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

Похоже, есть более простой способ сделать это (но только в MySQL):

select *
from (select * from mytable order by id, rev desc ) x
group by id

Пожалуйста, отметьте ответ пользователя Bohemian в этот вопрос за предоставление такого краткого и элегантного ответа на эту проблему.

РЕДАКТИРОВАТЬ: хотя это решение работает для многих людей, оно может быть нестабильным в долгосрочной перспективе, так как MySQL не гарантирует, что оператор GROUP BY будет возвращать значимые значения для столбцов, не входящих в список GROUP BY. Так что используйте это решение на свой страх и риск

5
По вопросам рекламы ammmcru@yandex.ru
Adblock
detector