loading..
Русский    English
16:36
листать

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

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

  1. SELECT @i:=@i+1 num,
  2.      trip_no, id_comp
  3.     FROM Trip, (SELECT @i:=0) X
  4.     WHERE ID_comp < 3
  5.     ORDER BY id_comp, trip_no;

num     id_comp     trip_no
1     1     1181
2     1     1182
3     1     1187
4     1     1188
5     1     1195
6     1     1196
7     2     1145
8     2     1146

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

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

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

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

Консоль
Выполнить
  1.     SELECT row_number() over(PARTITION BY id_comp ORDER BY id_comp,trip_no) num,
  2.      trip_no, id_comp
  3.     FROM Trip
  4.     WHERE ID_comp < 3
  5.     ORDER BY ID_comp, trip_no;

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

  1. SELECT
  2. CASE WHEN @comp=id_comp THEN @i:=@i+1 ELSE @i:=1 END num,
  3. @comp:=id_comp id_comp, trip_no
  4.     FROM Trip, (SELECT @i:=0, @comp:=0) X
  5.     WHERE ID_comp < 3
  6.     ORDER BY ID_comp, trip_no;

num     id_comp     trip_no
1     1     1181
2     1     1182
3     1     1187
4     1     1188
5     1     1195
6     1     1196
1     2     1145
2     2     1146

Или, коль скоро вы отошли от стандарта, можно использовать функцию IF, чтобы сократить запись:

  1. SELECT
  2. IF(@comp=id_comp, @i:=@i+1, @i:=1) num,
  3. @comp:=id_comp id_comp, trip_no
  4.     FROM Trip, (SELECT @i:=0, @comp:=0) X
  5.     WHERE ID_comp < 3
  6.     ORDER BY id_comp, trip_no;

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

Bookmark and Share
Страницы: 1 2
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INFORMATION_SCHEMA INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker Больше тэгов
Учебник обновлялся
несколько дней назад
https://exchangesumo.com/obmen/WEXRUB-USTMRUB/
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100