Как получить значения последних дат в месяц в MySQL [duplicate]

Я модифицировал Get-WmiCustom Daniel Muscetta для поддержки передачи учетных данных.

Я знаю, что этот пост немного стар, надеюсь, это помогает кому-то другому.

# Define modified custom get-wmiobject for timeout with credential from http://blogs.msdn.com/b/dmuscett/archive/2009/05/27/get_2d00_wmicustom.aspx
Function Get-WmiCustom([string]$Class,[string]$ComputerName,[string]$Namespace = "root\cimv2",[int]$Timeout=15, [pscredential]$Credential) 
{ 
    $ConnectionOptions = new-object System.Management.ConnectionOptions
    $EnumerationOptions = new-object System.Management.EnumerationOptions

    if($Credential){
        $ConnectionOptions.Username = $Credential.UserName;
        $ConnectionOptions.SecurePassword = $Credential.Password;
    }


    $timeoutseconds = new-timespan -seconds $timeout 
    $EnumerationOptions.set_timeout($timeoutseconds)

    $assembledpath = "\\$Computername\$Namespace"
    #write-host $assembledpath -foregroundcolor yellow

    $Scope = new-object System.Management.ManagementScope $assembledpath, $ConnectionOptions 
    $Scope.Connect()

    $querystring = "SELECT * FROM " + $class 
    #write-host $querystring

    $query = new-object System.Management.ObjectQuery $querystring 
    $searcher = new-object System.Management.ManagementObjectSearcher 
    $searcher.set_options($EnumerationOptions) 
    $searcher.Query = $querystring 
    $searcher.Scope = $Scope

    trap { $_ } $result = $searcher.get()

    return $result 
}
881
задан DineshDB 26 March 2018 в 09:27
поделиться

29 ответов

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

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

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

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

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

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

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

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

Соединение с простым group-identifier, max-value-in-group Sub-query

В этом подходе вы сначала найдете 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

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

In этот подход, вы оставили присоединиться к таблице с собой. Равенство, конечно, идет в group-identifier. Затем два умных перемещения:

  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, таким образом, будут работать с вашей любимой РСУБД, независимо от ее «вкуса».

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

1402
ответ дан Adrian Carneiro 16 August 2018 в 11:20
поделиться
  • 1
    Я знаю, что MySQL позволяет добавлять неагрегатные поля в группу сгруппированных по & quot; запрос, но я считаю, что это бесполезно. Попробуйте запустить select id, max(rev), rev from YourTable group by id, и вы понимаете, что я имею в виду. Не торопитесь и постарайтесь это понять – Adrian Carneiro 12 October 2011 в 21:05
  • 2
    @JasonMcCarrell Я рад, что этот ответ помог вам! Я получаю ваше мнение, поэтому я назвал его group_identifier, который может быть одним или несколькими столбцами. В вашем случае group_identifier представляет собой комбинацию имени и возраста – Adrian Carneiro 12 December 2012 в 18:50
  • 3
    Как мне заставить его возвращать только одну строку для каждой группы? Разве эти ответы не возвращают каждую строку в каждой группе со значением сравнения, равным максимальному значению? Например, предположим, что в наборе данных OP была вторая строка с id = 1, rev = 3. Не вернул бы ли он обе строки с id = 1, rev = 3? – Michael Lang 24 June 2013 в 23:42
  • 4
    @RobertChrist для произвольного разрыва связей с первой версией, просто добавьте DISTINCT ON (yt.id) после начального SELECT. Это сделало мой запрос занявшим в два раза длиннее. Итак, я не связываюсь, потому что в моем случае связи практически невозможны. – ma11hew28 14 March 2014 в 02:29
  • 5
    Почему первое решение будет работать? Функция max не будет выполняться для каждой группы, состоящей из одной строки, а не всех строк в целом. – Gherman 18 September 2014 в 09:24

вот еще одно решение, надеюсь, что это поможет кому-то

Select a.id , a.rev, a.content from Table1 a
inner join 
(SELECT id, max(rev) rev FROM Table1 GROUP BY id) x on x.id =a.id and x.rev =a.rev
1
ответ дан Abdul Samad 16 August 2018 в 11:20
поделиться

Вот хороший способ сделать это

Использовать следующий код:

with temp as  ( 
select count(field1) as summ , field1
from table_name
group by field1 )
select * from temp where summ = (select max(summ) from temp)
1
ответ дан Ashish Kakkad 16 August 2018 в 11:20
поделиться

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

SELECT A.KEYFIELD1, A.KEYFIELD2, A.FIELD3, A.FIELD4, A.FIELD5
  FROM MYFILE A
 WHERE RRN(A) IN
   (SELECT RRN(B) 
      FROM MYFILE B
     WHERE B.KEYFIELD1 = A.KEYFIELD1 AND B.KEYFIELD2 = A.KEYFIELD2
     ORDER BY B.FIELD5 DESC
     FETCH FIRST ROW ONLY)
1
ответ дан Axel 16 August 2018 в 11:20
поделиться
select * from yourtable
group by id
having rev=max(rev);
-1
ответ дан bensiu 16 August 2018 в 11:20
поделиться
  • 1
    Это не работает для меня в mysql. Не могли бы вы указать в документации, как это должно работать? Спасибо – Vajk Hermecz 23 January 2014 в 15:45
  • 2
    Это не работает в PostgreSQL. Он возвращает: ERROR: column "yourtable.content" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select * from messages – ma11hew28 13 March 2014 в 18:17
  • 3
    Также не работает в SQL Server. То, что предложение имеет фикцию, не может ссылаться на колонку rev. Этот ответ следует удалить. – Anssssss 8 February 2016 в 18:35

Отсортировано поле rev в обратном порядке, а затем сгруппировано по идентификатору, который дал первую строку каждой группы, которая является самой высокой величиной rev.

SELECT * FROM (SELECT * FROM table1 ORDER BY id, rev DESC) X GROUP BY X.id;

Проверено в http: //sqlfiddle.com/ со следующими данными

CREATE TABLE table1
    (`id` int, `rev` int, `content` varchar(11));

INSERT INTO table1
    (`id`, `rev`, `content`)
VALUES
    (1, 1, 'One-One'),
    (1, 2, 'One-Two'),
    (2, 1, 'Two-One'),
    (2, 2, 'Two-Two'),
    (3, 2, 'Three-Two'),
    (3, 1, 'Three-One'),
    (3, 3, 'Three-Three')
;

Это дало следующий результат в MySql 5.5 и 5.6

id  rev content
1   2   One-Two
2   2   Two-Two
3   3   Three-Two
1
ответ дан blokeish 16 August 2018 в 11:20
поделиться
  • 1
    Этот метод работал, но больше не работал. См. mariadb.com/kb/en/mariadb/… – Rick James 1 April 2017 в 22:02
  • 2
    Исходным тегом вопроса является «mysql». и я очень четко заявил, что мое решение было протестировано как с Mysql 5.5, так и с 5.6 в sqlfiddle.com. Я предоставил все шаги для независимой проверки решения. Я не делал никаких ложных утверждений о том, что мое решение работает с Mariadb. Mariadb - это не Mysql, его просто замена для Mysql, принадлежащая двум различным компаниям. Ваш комментарий поможет любому, кто пытается его реализовать в Мариадбе, но мой пост никоим образом не заслуживает отрицательного голоса, поскольку он четко отвечает на вопрос, который был задан. – blokeish 3 April 2017 в 00:34
  • 3
    Да, он работает в более старых версиях. И я использовал эту технику в прошлом, только чтобы ее сожгли, когда она перестала работать. Также MySQL (в 5.7?) Также будет игнорировать ORDER BY в подзапросе. Поскольку многие люди будут читать ваш ответ, я пытаюсь оттолкнуть их от техники, которая сломается в их будущем. (И я не дал вам -1 голосов.) – Rick James 3 April 2017 в 02:38

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

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

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

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

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

6
ответ дан Community 16 August 2018 в 11:20
поделиться
  • 1
    Кроме того, что это неправильно, так как нет гарантии, что порядок внутреннего запроса означает что-либо, и GROUP BY всегда гарантированно принимает первую встреченную строку. По крайней мере, в MySQL, и я бы принял все остальные. На самом деле я был в предположении, что MySQL просто игнорирует весь ORDER BY. Любая будущая версия или изменение конфигурации могут нарушить этот запрос. – Jannes 10 October 2014 в 11:14
  • 2
    @Jannes это интересное замечание :) Я приветствую вас ответить на мой вопрос, предоставляя доказательства: stackoverflow.com/questions/26301877/… – Yura 10 October 2014 в 15:41
  • 3
    @Jannes относительно GROUP BY не гарантированно возьмет первую встречную строку - вы совершенно правы - нашел эту проблему bugs.mysql.com/bug.php?id=71942 , которая просит предоставить такие гарантии. Обновит мой ответ сейчас – Yura 10 October 2014 в 15:59
  • 4
    Я думаю, что я помню, где я получил ORDER BY, который был отброшен: MySQL делает это с UNION, если вы ЗАКАЗЫВАЕТСЯ по внутренним запросам, это просто игнорируется: dev.mysql.com/doc/refman/5.0/en/union. html говорит «Если ORDER BY появляется без LIMIT в SELECT, он оптимизируется, потому что он не будет иметь никакого эффекта в любом случае». Я не видел такого утверждения для рассматриваемого вопроса здесь, но я не понимаю, почему он не мог этого сделать. – Jannes 11 October 2014 в 20:09
  • 5
    @Jannes hmmm, это интересно – Yura 13 October 2014 в 09:42

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

GOOD STUFF

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

ПОДХОД

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

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

Вот как это выглядит с помощью приведенного выше примера, написанного на 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

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

  • 3.2 становится 1003.201
  • 57 становится 1057,001
  • 923.88 становится 1923.881

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

35
ответ дан David Foster 16 August 2018 в 11:20
поделиться
  • 1
    Приоритет для вдохновенной взлома. – Barry Kelly 25 July 2013 в 15:58
  • 2
    Отличное решение, оно работает намного быстрее, чем соединение и другие предлагаемые решения. – danial 29 September 2014 в 23:10
  • 3
    Этот пост СО сделал мой месяц. Я использовал его несколько раз, и он намного быстрее, чем метод join. Очень умно. – vamin 25 March 2016 в 15:24

Если кто-то ищет Linq verson, это работает для меня:

public static IQueryable<BlockVersion> LatestVersionsPerBlock(this IQueryable<BlockVersion> blockVersions)
{
    var max_version_per_id = blockVersions.GroupBy(v => v.BlockId)
        .Select( v => new { BlockId = v.Key, MaxVersion = v.Max(x => x.Version) } );    

    return blockVersions.Where( v => max_version_per_id.Any(x => x.BlockId == v.BlockId && x.MaxVersion == v.Version) );
}
1
ответ дан Dirk Boer 16 August 2018 в 11:20
поделиться

Это работает для меня в sqlite3:

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

С * вы получаете дублированный столбец rev, но это не большая проблема.

-2
ответ дан Fredrik Eldh 16 August 2018 в 11:20
поделиться

SELECT * FROM Employee, где Employee.Salary in (выберите max (зарплата) из группы Employee Employee_id) ORDER BY Employee.Salary

1
ответ дан guru008 16 August 2018 в 11:20
поделиться
  • 1
    Вы должны отформатировать свой запрос и добавить с ним объяснения. – JRG 30 July 2017 в 18:38

Мне нравится использовать решение на основе NOT EXIST для этой проблемы:

SELECT id, rev
FROM YourTable t
WHERE NOT EXISTS (
   SELECT * FROM YourTable t WHERE t.id = id AND rev > t.rev
)
21
ответ дан HoldOffHunger 16 August 2018 в 11:20
поделиться
  • 1
    да, не существует, как это, как правило, было предпочтительным способом, а не левым соединением. В более старых версиях SQL-сервера это было быстрее, хотя я думаю, что теперь это не имеет никакого значения. Я обычно делаю SELECT 1 вместо SELECT *, опять же потому, что в предыдущих версиях он был быстрее. – EGP 8 October 2014 в 13:38
  • 2
    Это не отвечает на вопрос. Вопрос заключается в том, как получить данные для одной строки (как было задано «одна строка на идентификатор») в групповом запросе, где значение x является максимальным в каждой группе строк. Например, таблица заказов клиентов с несколькими заказами на одного клиента, где вы хотите получить наибольший заказ для каждого клиента. Ваш запрос может очень хорошо вернуть более одной строки для каждого клиента (если, например, два крупнейших заказа были размещены одним и тем же клиентом). – Aaron J Spetner 2 October 2017 в 06:39
  • 3
    "одна строка на идентификатор" & lt; - продолжайте читать, пожалуйста, и вы увидите & quot; и только самое большое & quot ;. Это логически эквивалентно самому большому. – HoldOffHunger 2 October 2017 в 12:17
  • 4
    Да, но он говорит «и». Это означает, что требования ОБОИХ одна строка на ID и только самая большая. Использование этого ответа не удовлетворяет первому требованию. Кроме того, вопрос подразумевает необходимость получения одной записи для ВСЕХ идентификаторов. Этот ответ требует знания количества идентификаторов заранее (для настройки LIMIT), для чего потребуется дополнительный код. Задача вопроса указана конкретно как поиск решения только для SQL. Наконец, даже если вы знаете количество уникальных идентификаторов, если есть несколько вхождений значения MAX, предложение LIMIT будет ошибочным. – Aaron J Spetner 3 October 2017 в 07:12
  • 5
    У меня не было такой же ситуации, как в исходном посте, но это самое легкое для понимания и простое и эффективное решение, к которому я столкнулся до сих пор. Я поражен тем, как все выродки и уроды пытаются обогнать друг друга, хвастаясь сложными / странными запросами. – sba 5 October 2017 в 14:58
  • 6
    @Aaron J Spetner: Я обновил решение, которое напрямую отвечает потребностям OP. – HoldOffHunger 27 November 2017 в 17:08

Третье решение, о котором я почти никогда не упоминал, имеет специфику MySQL и выглядит так:

SELECT id, MAX(rev) AS rev
 , 0+SUBSTRING_INDEX(GROUP_CONCAT(numeric_content ORDER BY rev DESC), ',', 1) AS numeric_content
FROM t1
GROUP BY id

Да, это выглядит ужасно (преобразование в строку и обратно и т. д.), но по моему опыту это обычно быстрее, чем другие решения. Возможно, это только для моих случаев использования, но я использовал его на таблицах с миллионами записей и множеством уникальных идентификаторов. Возможно, это связано с тем, что MySQL очень плохо оптимизирует другие решения (по крайней мере, в 5,0 дней, когда я придумал это решение).

Важно то, что GROUP_CONCAT имеет максимальную длину для строки, которую она может построить. Вероятно, вы захотите поднять этот предел, установив переменную group_concat_max_len. И имейте в виду, что это будет предел для масштабирования, если у вас есть большое количество строк.

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

4
ответ дан Jannes 16 August 2018 в 11:20
поделиться

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

Вы можете сделать это, используя IN, попробуйте это:

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

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

172
ответ дан Kevin Burton 16 August 2018 в 11:20
поделиться
  • 1
    Любопытно - в каком ядре базы данных мы можем использовать этот тип предложения WHERE? Это не поддерживается в SQL Server. – Kash 17 November 2011 в 19:04
  • 2
    оракул & amp; mysql (не уверен, что другие базы данных извиняются) – Kevin Burton 17 November 2011 в 20:03
  • 3
    Работает и на PostgreSQL. – lcguida 15 January 2014 в 19:43
  • 4
    Подтверждена работа в DB2 – coderatchet 29 January 2014 в 04:32
  • 5
    Не работает с SQLite. – Marcel Pfeiffer 26 October 2014 в 21:32

НЕ mySQL, но для других людей, которые находят этот вопрос и используют SQL, другой способ разрешить проблему использует Cross Apply в MS SQL

WITH DocIds AS (SELECT DISTINCT id FROM docs)

SELECT d2.id, d2.rev, d2.content
FROM DocIds d1
CROSS APPLY (
  SELECT Top 1 * FROM docs d
  WHERE d.id = d1.id
  ORDER BY rev DESC
) d2

Вот пример в SqlFiddle

2
ответ дан KyleMit 16 August 2018 в 11:20
поделиться
  • 1
    очень медленный по сравнению с другими методами - group by, windows, not exists – nahab 15 February 2018 в 14:40

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

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)
15
ответ дан Marc B 16 August 2018 в 11:20
поделиться

Я бы использовал это:

select t.*
from test as t
join
   (select max(rev) as rev
    from test
    group by id) as o
on o.rev = t.rev

Подзапрос SELECT не слишком эффективен, но в предложении JOIN кажется полезным. Я не эксперт в оптимизации запросов, но я пробовал в MySQL, PostgreSQL, FireBird, и он работает очень хорошо.

Вы можете использовать эту схему в нескольких соединениях и с предложением WHERE. Это мой рабочий пример (решение идентично вашей задаче с таблицей «твердое»):

select *
from platnosci as p
join firmy as f
on p.id_rel_firmy = f.id_rel
join (select max(id_obj) as id_obj
      from firmy
      group by id_rel) as o
on o.id_obj = f.id_obj and p.od > '2014-03-01'

Его спрашивают на таблицах с подростками таких записей записей, и он занимает менее 0,01 секунды на самом деле не слишком сильная машина.

Я бы не использовал пункт IN (как упоминается выше). IN предоставляется для использования с короткими списками констант, а не как фильтр запросов, построенный на подзапросе. Это связано с тем, что подзапрос в IN выполняется для каждой отсканированной записи, которая может сделать запрос очень медленным.

2
ответ дан Marek Wysmułek 16 August 2018 в 11:20
поделиться
  • 1
    Я думаю, что использование этого подзапроса в качестве CTE может по крайней мере повысить производительность – mmcrae 10 January 2017 в 19:52
  • 2
    Здравствуй! Для меня это похоже на то, что ваш первый запрос нуждается ... and o.id = t.id в конце (и подзапрос должен вернуть id для этого). Не так ли? – Dmitry Grekov 10 August 2018 в 11:37
SELECT * FROM t1 ORDER BY rev DESC LIMIT 1;
-4
ответ дан michaelb958 16 August 2018 в 11:20
поделиться
  • 1
    Это совершенно неверно. Он просто получает первую строку, где результирующий набор сортируется в порядке убывания. – man910 24 November 2013 в 10:20
  • 2
    OP был после «одной строки на идентификатор», а не только одной строки с наивысшим оборотом. – Ignitor 26 December 2013 в 16:24

Это решение делает только один выбор из YourTable, поэтому он быстрее. Он работает только для MySQL и SQLite (для SQLite удаляет DESC) в соответствии с тестом на sqlfiddle.com. Возможно, он может быть настроен для работы на других языках, с которыми я не знаком.

SELECT *
FROM ( SELECT *
       FROM ( SELECT 1 as id, 1 as rev, 'content1' as content
              UNION
              SELECT 2, 1, 'content2'
              UNION
              SELECT 1, 2, 'content3'
              UNION
              SELECT 1, 3, 'content4'
            ) as YourTable
       ORDER BY id, rev DESC
   ) as YourTable
GROUP BY id
1
ответ дан plavozont 16 August 2018 в 11:20
поделиться
  • 1
    Это, похоже, не работает для общего случая. И это вообще не работает в PostgreSQL, возвращаясь: ERROR: column "your table.reb" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT * – ma11hew28 13 March 2014 в 18:26
  • 2
    Извините, я не уточнил первый раз, на каком языке он работал. – plavozont 17 March 2014 в 07:11

Ни один из этих ответов не работал для меня.

Это то, что сработало для меня.

with score as (select max(score_up) from history)
select history.* from score, history where history.score_up = score.max
1
ответ дан qaisjp 16 August 2018 в 11:20
поделиться

Я использовал ниже, чтобы решить свою проблему. Сначала я создал временную таблицу и вставил максимальное значение rev на уникальный идентификатор.

CREATE TABLE #temp1
(
    id varchar(20)
    , rev int
)
INSERT INTO #temp1
SELECT a.id, MAX(a.rev) as rev
FROM 
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as a 
GROUP BY a.id
ORDER BY a.id

Затем я присоединил эти максимальные значения (# temp1) ко всем возможным комбинациям id / content. Делая это, я, естественно, отфильтровываю не максимальные комбинации id / content, и оставляю их только с максимальными значениями rev для каждого.

SELECT a.id, a.rev, content
FROM #temp1 as a
LEFT JOIN
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as b on a.id = b.id and a.rev = b.rev
GROUP BY a.id, a.rev, b.content
ORDER BY a.id
0
ответ дан Richard Ball 16 August 2018 в 11:20
поделиться

Многие, если не все, другие ответы здесь подходят для небольших наборов данных. Для масштабирования требуется больше внимания. См. здесь .

В нем обсуждается несколько более быстрых способов делать групповые макс и верхние N на группу.

1
ответ дан Rick James 16 August 2018 в 11:20
поделиться

Другой способ выполнения задания - использовать аналитическую функцию MAX () в предложении OVER PARTITION

SELECT t.*
  FROM
    (
    SELECT id
          ,rev
          ,contents
          ,MAX(rev) OVER (PARTITION BY id) as max_rev
      FROM YourTable
    ) t
  WHERE t.rev = t.max_rev 

. Другое решение OVER PARTITION, уже зарегистрированное в этом сообщении, -

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

Этот 2 SELECT хорошо работает на Oracle 10g.

0
ответ дан schlebe 16 August 2018 в 11:20
поделиться

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

select * from
(select * from table_name
order by id,rev desc) temp
group by id 
3
ответ дан seahawk 16 August 2018 в 11:20
поделиться
  • 1
    Это работает нормально для небольших таблиц, но занимает 6 проходов по всему набору данных, поэтому не быстро для больших таблиц. – Rick James 17 May 2017 в 00:48
  • 2
    Это запрос, который мне нужен, потому что были и другие столбцы. – Mike Viens 1 June 2018 в 19:07

Я разочарован тем, что в ответе не было предложено решение функции 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 Standard SQL: 2008, window ( или окна) доступны со всеми основными поставщиками сейчас. Существует множество типов ранговых функций, доступных для решения проблемы связи: RANK, DENSE_RANK, PERSENT_RANK.

29
ответ дан topchef 16 August 2018 в 11:20
поделиться
  • 1
    Я думаю, что он менее интуитивно понятен и потенциально менее ясен - но он может определенно работать / быть решением. – mmcrae 10 January 2017 в 17:52
  • 2
    интуиция - сложная вещь. Я нахожу его более интуитивным, чем другие ответы, поскольку он создает явную структуру данных, которая отвечает на вопрос. Но, опять же, интуиция - это другая сторона предвзятости ... – topchef 10 January 2017 в 19:22
  • 3
    Это может работать в MariaDB 10.2 и MySQL 8.0.2, но не раньше. – Rick James 1 April 2017 в 22:01
  • 4
    Наконец, я начал задаваться вопросом, почему этого не было. Это гораздо более "интуитивно понятный" чем подавляющее большинство «старой шляпы», ответы на этой странице и более эффективные решения практически во всех случаях, поскольку для этого требуется всего один проход данных. Большинство баз данных теперь поддерживают эти стандартные функции окна (MySQL опоздал, но будет от v8 вперед). – Used_By_Already 11 December 2017 в 01:42

Мне нравится делать это, оценивая записи по некоторым столбцам. В этом случае значения ранга rev группируются по id. Те, у кого выше rev, будут иметь более низкий рейтинг. Таким образом, наивысший rev будет иметь ранжирование 1.

select id, rev, content
from
 (select
    @rowNum := if(@prevValue = id, @rowNum+1, 1) as row_num,
    id, rev, content,
    @prevValue := id
  from
   (select id, rev, content from YOURTABLE order by id asc, rev desc) TEMP,
   (select @rowNum := 1 from DUAL) X,
   (select @prevValue := -1 from DUAL) Y) TEMP
where row_num = 1;

Не уверен, что введение переменных делает все это медленнее. Но, по крайней мере, я не дважды запрашиваю YOURTABLE.

1
ответ дан user5124980 16 August 2018 в 11:20
поделиться
  • 1
    Только пробованный подход в MySQL. У Oracle есть аналогичная функция для ранжирования записей. Идея тоже должна работать. – user5124980 16 July 2015 в 18:54

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

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 (подзапрос, leftjoin), основанные на измерениях MySQL с таблицей InnoDB размером ~ 1 миллион записей, размер группы: 1-3.

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

Наконец, поскольку leftjoin создает n * (n + 1) / 2, объединяется в группы, на его производительность может сильно влиять размер групп ...

55
ответ дан Vajk Hermecz 16 August 2018 в 11:20
поделиться
  • 1
    +1 для простоты и ясности – xagyg 17 March 2014 в 06:58
  • 2
    Это единственное, что до сих пор работало так, как мне было нужно, спасибо (нужно совпадение по имени, а не по id) – Doomed Mind 2 February 2017 в 16:27
  • 3
    Я не думаю, что это работает, если rev не уникален. – Pita 5 June 2017 в 21:13
  • 4
    @Pita no. он работает, даже если rev не уникален – Pradeep Kumar Prabaharan 29 September 2017 в 16:44
  • 5
    Хорошая точка для упоминания индекса, необходимого для простого поиска (видимо, не может плюс 1 в комментариях больше) – Jared Becksfort 13 November 2017 в 19:10

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

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

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

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

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

  • ORDER BY Employee.Salary DESC: Закажите результаты по зарплате с наивысшими зарплатами.
  • LIMIT 1: Верните только один результат.

Понимание этого подхода, решение любой из этих подобных проблем становится тривиальным: получить сотрудника с наименьшей зарплатой (сменить DESC на ASC), получить топ-десять сотрудников (изменить LIMIT 1 до LIMIT 10), сортировать по средства другого поля (изменение ЗАКАЗА ПО РАБОТЕ. Сотрудник по заказу Employee.Commission) и т. д.

21
ответ дан HoldOffHunger 16 August 2018 в 11:20
поделиться
  • 1
    Это не отвечает на вопрос. Вопрос заключается в том, как получить данные для одной строки (как было задано «одна строка на идентификатор») в групповом запросе, где значение x является максимальным в каждой группе строк. Например, таблица заказов клиентов с несколькими заказами на одного клиента, где вы хотите получить наибольший заказ для каждого клиента. Ваш запрос может очень хорошо вернуть более одной строки для каждого клиента (если, например, два крупнейших заказа были размещены одним и тем же клиентом). – Aaron J Spetner 2 October 2017 в 06:39
  • 2
    "одна строка на идентификатор" & lt; - продолжайте читать, пожалуйста, и вы увидите & quot; и только самое большое & quot ;. Это логически эквивалентно самому большому. – HoldOffHunger 2 October 2017 в 12:17
  • 3
    Да, но он говорит «и». Это означает, что требования ОБОИХ одна строка на ID и только самая большая. Использование этого ответа не удовлетворяет первому требованию. Кроме того, вопрос подразумевает необходимость получения одной записи для ВСЕХ идентификаторов. Этот ответ требует знания количества идентификаторов заранее (для настройки LIMIT), для чего потребуется дополнительный код. Задача вопроса указана конкретно как поиск решения только для SQL. Наконец, даже если вы знаете количество уникальных идентификаторов, если есть несколько вхождений значения MAX, предложение LIMIT будет ошибочным. – Aaron J Spetner 3 October 2017 в 07:12
  • 4
    У меня не было такой же ситуации, как в исходном посте, но это самое легкое для понимания и простое и эффективное решение, к которому я столкнулся до сих пор. Я поражен тем, как все выродки и уроды пытаются обогнать друг друга, хвастаясь сложными / странными запросами. – sba 5 October 2017 в 14:58
  • 5
    @Aaron J Spetner: Я обновил решение, которое напрямую отвечает потребностям OP. – HoldOffHunger 27 November 2017 в 17:08

Как насчет этого:

select all_fields.*  
from  (select id, MAX(rev) from yourtable group by id) as max_recs  
left outer join yourtable as all_fields  
on max_recs.id = all_fields.id
2
ответ дан KyleMit 16 August 2018 в 11:20
поделиться
Другие вопросы по тегам:

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