номер строки в mysql запросе

MySQL номер строки

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

Получать данные будем из таблицы истории получения очков, которая зовётся «history». Выглядит она так:

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

SELECT SUM(POINTS) as ‘POINTS’, NAME FROM `history` GROUP BY NAME ORDER BY SUM(POINTS) DESC;

База вернёт следующую таблицу:

Нумеровка делается через переменную, добавляемую к выводу. Казалось бы, можно сделать всё просто:

SELECT @rank:=@rank+1 AS ‘RANK’, SELECT SUM(POINTS) as ‘POINTS’, NAME FROM `history` GROUP BY NAME ORDER BY SUM(POINTS) DESC;

Но если сделать такой запрос, то результат будет плачевным. Назначение рейтинга произойдёт после группировки, но до сортировки ORDER BY SUM(POINTS). Выглядеть результат будет так:

Цифры ранга идут не по порядку: 1, 3, 2. Чтобы исправить положение необходимо добавлять столбец ранга после группировки и сортировки, в отдельном запросе. По логике, получается SELECT в SELECT. Синтаксис будет такой:

SET @rank=0;
SELECT @rank:=@rank+1 AS ‘RANK’, POINTS, NAME FROM
(

SELECT SUM(POINTS) as ‘POINTS’, NAME
FROM `history` GROUP BY NAME ORDER BY SUM(POINTS) DESC

Обратите внимание на окончание запроса. Без «as t;» написать запрос нельзя, иначе будет ошибка «Every derived table must have its own alias!«.

Результат такого запроса будет выглядеть как нормальная ранговая таблица. С правильным расположением цифр ранга:

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

SELECT @rank:=@rank+1 AS ‘RANK’, NAME FROM
(

SELECT SUM(POINTS), NAME
FROM `history` GROUP BY NAME ORDER BY SUM(POINTS) DESC

) as t2
WHERE NAME = ‘Виталий’;

Обратите внимание на хвосты: «as t1», «as t2». Нужны разные псевдонимы, иначе будет ошибка «Every derived table must have its own alias!«.

Конечно же, на реальном проекте такая таблица будет содержать id пользователя, а не его имя. Потому что в строке правды нет, а в числе есть 🙂

Источник

Функция ROW_NUMBER стр. 2

В MySQL ранжирующих/оконных функций не было до версии 8.0, однако была возможность использовать переменные непосредственно в запросе SQL. В частности, с помощью переменных можно решить задачу нумерации строк запроса. Продемонстрируем это на примере, который рассматривался на предыдущей странице.

В третьей строке запроса выполняется инициализация переменной и присваивается ей начальное значение. В итоге каждая строка таблицы Trip будет соединяться со строкой из одного столбца, содержащего 0 (просто декартово произведение).

В первой строке запроса значение переменной инкрементируется на 1, что происходит при вычислении каждой следующей строки в порядке, заданном предложением ORDER BY. В итоге мы получаем нумерацию строк в порядке сортировки.

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

Мы также можем перенумеровать строки для каждой компании отдельно, т.е. сымитировав поведение PARTITION BY в запросе

Идея решения состоит в следующем. Введем еще одну переменную для хранения номера компании. При инициализации присвоим ей несуществующий номер (например, 0). Затем для каждой строки будем проверять, совпадает ли номер с номером компании текущей строки. Если значения совпадают, будем инкрементировать, если нет, сбрасывать в 1. Наконец, будем присваивать переменной номер компании из текущей строки. Дело в том, что проверка выполняется до присвоения, тем самым мы сравниваем текущее значение номера компании с номером компании из предыдущей строки (в заданном порядке сортировки). Теперь сам запрос.

Проверить эти запросы вы можете из консоли, выбрав из списка MySQL.

Источник

Эмуляция функции row_number() в MySQL

Нумерация строк

Чтобы пронумеровать строки, мы должны объявить переменную запроса. Продемонстрируем этот подход на примере простой таблицы, содержащей список работников предприятия ( employees ). Следующий запрос выбирает 5 работников из таблицы, присваивая им номера по порядку, начиная с 1:

В выше приведённом запросе мы:

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

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

Возобновление нумерации в группах

Нам нужно сформировать список платежей, в котором каждому платежу будет соответствовать определённый порядковый номер. Для того чтобы получить требуемый результат, нам понадобятся две переменные: одна – с порядковым номером строки, другая – для хранения идентификатора клиента из предыдущей строки, чтобы сравнить его с текущим. Наш запрос будет выглядеть так:

Мы использовали оператор CASE для вычисления условия: если номер клиента остаётся прежним, мы увеличиваем номер строки на 1, в противном случае мы устанавливаем номер строки равным 1. Результат будет тем же, что и на выше приведённом скриншоте.

Теперь добьёмся того же результата, используя технику производной таблицы и перекрёстного запроса:

Источник

Номер строки () в MySQL

тогда я мог бы, например, добавить условие, чтобы ограничить intRow 1, чтобы получить одну строку с наибольшим col3 для каждого

21 ответов

Я хочу строку с одним самым высоким col3 для каждой пары (col1, col2).

Это groupwise для максимальной один из наиболее часто задаваемых вопросов по SQL (поскольку кажется, что это должно быть легко, но на самом деле это не так).

Я часто пополнел для null-self-join:

» получите строки в таблице,для которых нет другой строки с соответствующим col1, col2 имеет более высокий col3.(Вы заметите это и большинство других groupwise-максимальные решения вернут несколько строк, если несколько строк имеют одинаковый col1, col2, col3. Если это проблема, вам может понадобиться некоторая пост-обработка.)

в MySQL нет функции ранжирования. Самое близкое, что вы можете получить, это использовать переменную:

Итак, как это будет работать в моем случае? Мне нужны две переменные, по одной для каждого из col1 и col2? Col2 нужно будет каким-то образом сбросить, когда col1 изменится.

да. Если бы это был Oracle, вы могли бы использовать функцию LEAD для пика при следующем значении. К счастью, Quassnoi крышки логика для того, что вам нужно реализовать в В MySQL.

Я всегда в конечном итоге следую этой схеме. Учитывая эту таблицу:

вы можете получить такой результат:

запустив этот запрос, который не нуждается в какой-либо переменной, определенной:

надеюсь, что это поможет!

проверьте эту статью, она показывает, как имитировать SQL ROW_NUMBER() с разделом в MySQL. Я столкнулся с этим же сценарием в реализации WordPress. Мне нужен ROW_NUMBER (), и его там не было.

в Примере в статье используется один раздел по полю. Чтобы разбить на дополнительные поля, вы можете сделать что-то вроде этого:

использовать concat_ws обрабатывает null. Я протестировал это против 3 полей, используя int, date и varchar. Надеюсь, это поможет. Проверьте статью, поскольку она разбивает этот запрос и объясняет его.

Я бы также проголосовал за решение мости Мостачо с незначительной модификацией его кода запроса:

что даст тот же результат:

С той лишь разницей,что запрос не использует JOIN и GROUP BY, полагаясь на вложенный select.

С MySQL 8.0.0 и выше вы можете изначально использовать оконные функции.

MySQL теперь поддерживает оконные функции, которые для каждой строки из запроса выполняют вычисление, используя строки, связанные с этой строкой. К ним относятся такие функции, как RANK (), LAG () и NTILE(). Кроме того, в качестве оконных функций теперь можно использовать несколько существующих агрегатных функций; например, SUM() и AVG ().

возвращает номер текущей строки в своем разделе. Номера строк варьируются от 1 до количества строк раздела.

ORDER BY влияет на порядок, в котором нумеруются строки. Без ORDER BY нумерация строк не определена.

Я бы определил функции:

тогда я мог бы сделать:

теперь у вас нет подзапросов, которые вы не можете иметь в вид.

запрос для row_number в mysql

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

раздел по столбцам просто сравнивается с » = «и разделяется на «и». Порядок по столбцам будет сравниваться с » «и разделяться «или».

Я нашел это очень гибким, даже если это немного дорого.

функциональность rownumber не может быть имитирована. Вы можете получить ожидаемые результаты, но на каком-то этапе вы, скорее всего, будете разочарованы. Вот что говорит документация mysql:

для других операторов, таких как SELECT, вы можете получить ожидаемые результаты, но это не гарантируется. В следующем заявлении вы можете подумать, что MySQL сначала оценит @a, а затем выполнит второе назначение: Выберите @a, @a:=@a+1. ; Однако, порядок оценки выражения, включающие пользовательские переменные, не определены.

с уважением, Георгий.

MariaDB 10.2 реализует «оконные функции», включая RANK (), ROW_NUMBER() и несколько других вещей:

основываясь на разговоре в Percona Live в этом месяце, они достаточно хорошо оптимизированы.

синтаксис идентичен коду в вопросе.

немного поздно, но может также помочь тем, кто ищет ответы.

Between rows / row_number example-рекурсивный запрос, который может использоваться в любом SQL:

Это позволяет той же функциональности, что ROW_NUMBER () и PARTITION BY обеспечивает быть достигнутым в MySQL

также немного поздно, но сегодня у меня была такая же потребность, поэтому я искал в Google и, наконец, простой общий подход, найденный здесь в статье Pinal Davehttp://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/

Beacuse мы хотим разбить на два столбца, я бы создал Задайте переменную во время итерации, чтобы определить, была ли запущена новая группа.

3 означает, что при первом параметре MAKE_SET я хочу оба значения в наборе (3=1/2). Конечно, если у нас нет двух или более столбцов, строящих группы, мы можем исключить операцию MAKE_SET. Конструкция точно такая же. Это работает для меня по мере необходимости. Большое спасибо Pinal Dave за его четкую демонстрацию.

Я не вижу простого ответа, охватывающего часть» раздел по», Поэтому вот мой:

в этом простом примере Я поставил только один, но вы можете иметь несколько» разделов по » частям

Источник

Функция окна номера строки MySQL

В MySQL метод ROW NUMBER () содержит хронологический номер для каждой строки внутри раздела. Это просто своего рода оконная фишка. Число строк начинается с 1 с числа строк внутри раздела. Помните, что до версии 8.0 MySQL не разрешал функцию ROW NUMBER (), однако он предлагал переменную сеанса, которая помогает имитировать эту функцию. В этом руководстве мы узнаем больше о функциональности MySQL ROW NUMBER () и создадим последовательный номер для каждой строки в коллекции результатов. В MySQL методы ROW_NUMBER () используются либо с последующими предложениями:

Синтаксис

Давайте откроем клиентскую оболочку командной строки MySQL из приложений и введем пароль для входа в систему.

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

Пример 1: ROW_NUMBER () с использованием предложения ORDER BY

Мы будем использовать ту же таблицу, чтобы подробно описать некоторые примеры функции номера строки. Мы берем пример функции ROW_NUMBER (), за которой следует Over (), при этом используем только предложение ORDER BY. Мы производили выборку всех записей при нумерации строк в соответствии с порядком столбца «Цена». Мы дали имя «row_num» столбцу, в котором будут храниться номера строк. Давайте попробуем следующую команду сделать это.

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

Давайте выполним тот же запрос, за которым следует предложение ORDER BY, используя порядок сортировки столбца «Возраст». Результат будет указан в столбце «Возраст».

Пример 2: ROW_NUMBER () с использованием предложения PARTITION BY

Мы будем использовать единственное предложение PARTITION BY в запросе ROW_NUMBER () для проверки результатов. Мы использовали запрос SELECT для выборки записей, за которыми следовали ROW_NUMBER () и предложение OVER, при разделении таблицы в соответствии со столбцом «Цвет». Выполните добавленную ниже команду в командной оболочке.

В результате вы можете увидеть, что нумерация строк была назначена в разделах в соответствии с порядком сортировки цветов. Поскольку у нас есть 4 значения для цвета «Черный», который занимает 4 строки. Вот почему у него четырехрядные числа от 1 до 4 и наоборот.

Попробуйте тот же пример, на этот раз разделенный столбцом «Пол». Как мы знаем, в этой таблице всего два пола, поэтому будет сформировано 2 раздела. Самки занимают 9 рядов, поэтому они имеют нумерацию от 1 до 9. У самцов 8 значений, поэтому от 1 до 8.

Пример 3: ROW_NUMBER () с использованием PARTITION BY и ORDER BY

Мы выполнили два приведенных выше примера в командной строке MySQL, теперь пора выполнить пример ROW_NUMBER () в MySQL Workbench 8.0. Итак, откройте MySQL Workbench 8.0 из приложений. Подключите MySQL Workbench к корневой базе данных локального хоста, чтобы начать работу.

В левой части MySQL Workbench вы найдете панель схемы, взорвите навигатор. На этой панели схемы вы найдете список баз данных. В списке баз данных у вас будут разные таблицы и хранимые процедуры, как вы можете видеть на изображении ниже. В нашей базе данных есть разные таблицы. Мы откроем таблицу order1 с помощью команды SELECT в области запроса, чтобы начать использовать ее для реализации функции ROW_NUMBER ().

Таблица «order1» была отображена в виде сетки, как показано ниже. Вы можете видеть, что у него есть 4 поля столбца: id, Region, Status и OrderNo. Мы будем извлекать все записи из этой таблицы, одновременно используя предложения ORDER BY и PARTITION BY.

В области запроса MySQL Workbench 8.0 введите показанный ниже запрос. Запрос был запущен с предложением SELECT, получая все записи, за которыми следует функция ROW_NUMBER () вместе с предложением OVER. После предложения OVER мы указали столбец «Статус», за которым следует оператор «PARTITION BY», чтобы разделить таблицу на разделы в соответствии с этой таблицей. Предложение ORDER BY используется для упорядочивания таблицы по убыванию в соответствии со столбцом «Регион». Номера строк будут сохранены в столбце «row_num». Нажмите на значок вспышки, чтобы выполнить эту команду.

Будет показан результат, показанный ниже. Прежде всего, таблица разделена на две части по значениям столбца «Статус». После этого он был представлен в порядке убывания столбца «Регион», и разделам были присвоены номера строк.

Заключение

Наконец, мы завершили все необходимые примеры использования функции ROW_NUMBER () в MySQL Workbench и клиентской оболочке командной строки MySQL.

Источник