Intereting Posts
Можно ли выбрать без таблиц с несколькими строками? Можно ли использовать столбец, который я выбрал позже в запросе? привязка возвращается к целевой таблице Как хранить данные, тип которых может быть числовым, дата или строка в mysql Лучшая практика при обновлении отдельных полей в записи базы данных Как использовать тип BOOLEAN в инструкции SELECT TSQL – возвращаемое значение из sp, если sp не содержит возврата Исключение EF 4.1 «Поставщик не вернул строку ProviderManifestToken» Функция MySQL позволяет найти количество рабочих дней между двумя датами ORA-00972: идентификатор слишком длинный Возможно ли использовать одну и ту же таблицу дважды в выбранном запросе? Как подсчитать экземпляры символов в столбце SQL Выбор SQL: двухмерный выбор с переменным числом столбцов Простое начинание Sql как получить имена столбцов таблицы в качестве записей в наборе столбцов результата?

Получать записи с максимальным значением для каждой группы сгруппированных SQL-результатов

Как вы получаете строки, содержащие максимальное значение для каждого сгруппированного набора?

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

Учитывая таблицу, подобную приведенной ниже, с персональными, групповыми и возрастными столбцами, как бы вы получили самого старшего в каждой группе? (Завязка внутри группы должна дать первый алфавитный результат)

Person | Group | Age --- Bob | 1 | 32 Jill | 1 | 34 Shawn| 1 | 42 Jake | 2 | 29 Paul | 2 | 36 Laura| 2 | 39 

Желаемый набор результатов:

 Shawn | 1 | 42 Laura | 2 | 39 

В mysql есть супер-простой способ:

 select * from (select * from mytable order by `Group`, age desc, Person) x group by `Group` 

Это работает, потому что в mysql вам запрещено группировать столбцы без группы, и в этом случае mysql просто возвращает первую строку. Решение состоит в том, чтобы сначала упорядочить данные таким образом, чтобы для каждой группы сначала была строка, а затем группировать по столбцам, для которых требуется значение.

Вы избегаете сложных подзапросов, которые пытаются найти max() т. Д., А также проблемы с возвратом нескольких строк, если их более одного с одинаковым максимальным значением (как это делали другие ответы)

Примечание. Это решение только для mysql . Все остальные базы данных, которые я знаю, будут вызывать синтаксическую ошибку SQL с сообщением, что «неагрегированные столбцы не указаны в группе по предложению» или аналогичны. Некоторые «пуристы» считают этот синтаксис работой дьявола, но это так чертовски удобно!

Версия 5.7:

Начиная с версии 5.7, параметр sql-mode по умолчанию включает ONLY_FULL_GROUP_BY , поэтому для выполнения этой работы вы не должны иметь эту возможность (отредактируйте файл параметров для сервера, чтобы удалить этот параметр).

Правильное решение:

 SELECT o.* FROM `Persons` o # 'o' from 'oldest person in group' LEFT JOIN `Persons` b # 'b' from 'bigger age' ON o.Group = b.Group AND o.Age < b.Age WHERE b.Age is NULL # bigger age not found 

Как это работает:

Он соответствует каждой строке из o причем все строки из b имеют одинаковое значение в столбце Group и большее значение в столбце Age . Любая строка из o не имеющая максимального значения ее группы в столбце Age будет соответствовать одной или нескольким строкам из b .

LEFT JOIN делает его совпадающим с самым старым человеком в группе (включая людей, которые находятся в одиночестве в своей группе), с строкой, полной NULL s от b («не самый большой возраст в группе»).
Использование INNER JOIN делает эти строки не соответствующими, и они игнорируются.

Предложение WHERE хранит только строки, имеющие NULL s в полях, извлеченных из b . Это самые старые люди из каждой группы.

Дальнейшие чтения

Это решение и многие другие объясняются в книге SQL Antipatterns: устранение ошибок программирования баз данных

Вы можете присоединиться к подзапросу, который тянет MAX(Group) и Age . Этот метод переносится по большинству РСУБД.

 SELECT yourtable.* FROM yourtable JOIN ( SELECT `Group`, MAX(Age) AS age FROM yourtable GROUP BY `Group` ) maxage /* join subquery against both Group and Age values */ ON yourtable.`Group` = maxage.`Group` AND yourtable.Age = maxage.age 

Мое простое решение для SQLite (и, вероятно, MySQL):

 SELECT *, MAX(age) FROM mytable GROUP BY `Group`; 

Однако это не работает в PostgreSQL и, возможно, в некоторых других платформах.

В PostgreSQL вы можете использовать предложение DISTINCT ON :

 SELECT DISTINCT ON ("group") * FROM "mytable" ORDER BY "group", "age" DESC; 

Использование метода ранжирования.

 SELECT @rn := CASE WHEN @prev_grp <> groupa THEN 1 ELSE @rn+1 END AS rn, @prev_grp :=groupa, person,age,groupa FROM users,(SELECT @rn := 0) r HAVING rn=1 ORDER BY groupa,age DESC,person 

Использование CTE – общие выражения таблицы:

 WITH MyCTE(MaxPKID, SomeColumn1) AS( SELECT MAX(a.MyTablePKID) AS MaxPKID, a.SomeColumn1 FROM MyTable1 a GROUP BY a.SomeColumn1 ) SELECT b.MyTablePKID, b.SomeColumn1, b.SomeColumn2 MAX(b.NumEstado) FROM MyTable1 b INNER JOIN MyCTE c ON c.MaxPKID = b.MyTablePKID GROUP BY b.MyTablePKID, b.SomeColumn1, b.SomeColumn2 --Note: MyTablePKID is the PrimaryKey of MyTable 

Решение axiac – это то, что лучше всего подходит для меня в конце. Однако у меня была дополнительная сложность: рассчитанное «максимальное значение», полученное из двух столбцов.

Давайте используем тот же пример: мне бы хотелось, чтобы самый старший человек в каждой группе. Если есть люди, которые одинаково стары, возьмите самого высокого человека.

Я должен был выполнить левое соединение два раза, чтобы получить такое поведение:

 SELECT o1.* WHERE (SELECT o.* FROM `Persons` o LEFT JOIN `Persons` b ON o.Group = b.Group AND o.Age < b.Age WHERE b.Age is NULL) o1 LEFT JOIN (SELECT o.* FROM `Persons` o LEFT JOIN `Persons` b ON o.Group = b.Group AND o.Age < b.Age WHERE b.Age is NULL) o2 ON o1.Group = o2.Group AND o1.Height < o2.Height WHERE o2.Height is NULL; 

Надеюсь это поможет! Я думаю, должен быть лучший способ сделать это, хотя …

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

 SELECT SUBSTRING_INDEX(GROUP_CONCAT(column_x ORDER BY column_y),',',1) AS xyz, column_z FROM table_name GROUP BY column_z; 

Он использует GROUP_CONCAT для создания упорядоченного списка concat, а затем подстроку только к первой.

Вы также можете попробовать

 SELECT * FROM mytable WHERE age IN (SELECT MAX(age) FROM mytable GROUP BY `Group`) ; 

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

Источник: http://dev.mysql.com/doc/refman/5.0/ru/group-by-functions.html#function_group-concat

 SELECT person, group, GROUP_CONCAT( DISTINCT age ORDER BY age DESC SEPARATOR ', follow up: ' ) FROM sql_table GROUP BY group; 

Не уверен, что MySQL имеет функцию row_number. Если это так, вы можете использовать его для получения желаемого результата. На SQL Server вы можете сделать что-то похожее:

 CREATE TABLE p ( person NVARCHAR(10), gp INT, age INT ); GO INSERT INTO p VALUES ('Bob', 1, 32); INSERT INTO p VALUES ('Jill', 1, 34); INSERT INTO p VALUES ('Shawn', 1, 42); INSERT INTO p VALUES ('Jake', 2, 29); INSERT INTO p VALUES ('Paul', 2, 36); INSERT INTO p VALUES ('Laura', 2, 39); GO SELECT t.person, t.gp, t.age FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY gp ORDER BY age DESC) row FROM p ) t WHERE t.row = 1; 

пусть имя таблицы будет людьми

 select O.* -- > O for oldest table from people O , people T where O.grp = T.grp and O.Age = (select max(T.age) from people T where O.grp = T.grp group by T.grp) group by O.grp; 

Если требуется идентификатор (и все связи) из таблицы

 SELECT * FROM mytable WHERE id NOT IN ( SELECT A.id FROM mytable AS A JOIN mytable AS B ON A. GROUP = B. GROUP AND A.age < B.age ) 
 with CTE as (select Person, [Group], Age, RN= Row_Number() over(partition by [Group] order by Age desc) from yourtable)` `select Person, Age from CTE where RN = 1` 

Я бы не использовал Group как имя столбца, так как это зарезервированное слово. Однако после SQL будет работать.

 SELECT a.Person, a.Group, a.Age FROM [TABLE_NAME] a INNER JOIN ( SELECT `Group`, MAX(Age) AS oldest FROM [TABLE_NAME] GROUP BY `Group` ) b ON a.Group = b.Group AND a.Age = b.oldest