Получение значения предыдущей записи с использованием ROW_NUMBER() в SQL Server. Rownum в sql

sql - Функция SQL Row_Number() в разделе Where Where

на основе ответа OP на вопрос:

Пожалуйста, просмотрите эту ссылку. Имея другое решение, которое выглядит работая для человека, который спросил вопрос. Я пытаюсь выяснить решение, подобное этому.

Разбитый запрос с использованием сортировки по разным столбцам с использованием ROW_NUMBER() OVER() в SQL Server 2005

~ Джозеф

"метод 1" подобен запросу OP из связанного вопроса, а "метод 2" похож на запрос из выбранного ответа. Вам нужно было посмотреть код, связанный в этом , чтобы узнать, что действительно происходит, поскольку код в выбранном ответе был изменен, чтобы заставить его работать. Попробуйте следующее:

DECLARE @YourTable table (RowID int not null primary key identity, Value1 int, Value2 int, value3 int) SET NOCOUNT ON INSERT INTO @YourTable VALUES (1,1,1) INSERT INTO @YourTable VALUES (1,1,2) INSERT INTO @YourTable VALUES (1,1,3) INSERT INTO @YourTable VALUES (1,2,1) INSERT INTO @YourTable VALUES (1,2,2) INSERT INTO @YourTable VALUES (1,2,3) INSERT INTO @YourTable VALUES (1,3,1) INSERT INTO @YourTable VALUES (1,3,2) INSERT INTO @YourTable VALUES (1,3,3) INSERT INTO @YourTable VALUES (2,1,1) INSERT INTO @YourTable VALUES (2,1,2) INSERT INTO @YourTable VALUES (2,1,3) INSERT INTO @YourTable VALUES (2,2,1) INSERT INTO @YourTable VALUES (2,2,2) INSERT INTO @YourTable VALUES (2,2,3) INSERT INTO @YourTable VALUES (2,3,1) INSERT INTO @YourTable VALUES (2,3,2) INSERT INTO @YourTable VALUES (2,3,3) INSERT INTO @YourTable VALUES (3,1,1) INSERT INTO @YourTable VALUES (3,1,2) INSERT INTO @YourTable VALUES (3,1,3) INSERT INTO @YourTable VALUES (3,2,1) INSERT INTO @YourTable VALUES (3,2,2) INSERT INTO @YourTable VALUES (3,2,3) INSERT INTO @YourTable VALUES (3,3,1) INSERT INTO @YourTable VALUES (3,3,2) INSERT INTO @YourTable VALUES (3,3,3) SET NOCOUNT OFF DECLARE @PageNumber int DECLARE @PageSize int DECLARE @SortBy int SET @PageNumber=3 SET @PageSize=5 SET @SortBy=1 --SELECT * FROM @YourTable --Method 1 ;WITH PaginatedYourTable AS ( SELECT RowID,Value1,Value2,Value3 ,CASE @SortBy WHEN 1 THEN ROW_NUMBER() OVER (ORDER BY Value1 ASC) WHEN 2 THEN ROW_NUMBER() OVER (ORDER BY Value2 ASC) WHEN 3 THEN ROW_NUMBER() OVER (ORDER BY Value3 ASC) WHEN -1 THEN ROW_NUMBER() OVER (ORDER BY Value1 DESC) WHEN -2 THEN ROW_NUMBER() OVER (ORDER BY Value2 DESC) WHEN -3 THEN ROW_NUMBER() OVER (ORDER BY Value3 DESC) END AS RowNumber FROM @YourTable --WHERE ) SELECT RowID,Value1,Value2,Value3,RowNumber ,@PageNumber AS PageNumber, @PageSize AS PageSize, @SortBy AS SortBy FROM PaginatedYourTable WHERE RowNumber>=(@PageNumber-1)*@PageSize AND RowNumber<=(@PageNumber*@PageSize)-1 ORDER BY RowNumber -------------------------------------------- --Method 2 ;WITH PaginatedYourTable AS ( SELECT RowID,Value1,Value2,Value3 ,ROW_NUMBER() OVER ( ORDER BY CASE @SortBy WHEN 1 THEN Value1 WHEN 2 THEN Value2 WHEN 3 THEN Value3 END ASC ,CASE @SortBy WHEN -1 THEN Value1 WHEN -2 THEN Value2 WHEN -3 THEN Value3 END DESC ) RowNumber FROM @YourTable --WHERE more conditions here ) SELECT RowID,Value1,Value2,Value3,RowNumber ,@PageNumber AS PageNumber, @PageSize AS PageSize, @SortBy AS SortBy FROM PaginatedYourTable WHERE RowNumber>=(@PageNumber-1)*@PageSize AND RowNumber<=(@PageNumber*@PageSize)-1 --AND more conditions here ORDER BY CASE @SortBy WHEN 1 THEN Value1 WHEN 2 THEN Value2 WHEN 3 THEN Value3 END ASC ,CASE @SortBy WHEN -1 THEN Value1 WHEN -2 THEN Value2 WHEN -3 THEN Value3 END DESC

ВЫВОД:

RowID Value1 Value2 Value3 RowNumber PageNumber PageSize SortBy ------ ------ ------ ------ ---------- ----------- ----------- ----------- 10 2 1 1 10 3 5 1 11 2 1 2 11 3 5 1 12 2 1 3 12 3 5 1 13 2 2 1 13 3 5 1 14 2 2 2 14 3 5 1 (5 row(s) affected RowID Value1 Value2 Value3 RowNumber PageNumber PageSize SortBy ------ ------ ------ ------ ---------- ----------- ----------- ----------- 10 2 1 1 10 3 5 1 11 2 1 2 11 3 5 1 12 2 1 3 12 3 5 1 13 2 2 1 13 3 5 1 14 2 2 2 14 3 5 1 (5 row(s) affected)

qaru.site

[analytic-functions] Функция SQL Row_Number () в разделе Where Where [tsql] [sql-server]

В ответ на комментарии к ответу rexem в отношении того, будет ли встроенный просмотр или CTE быстрее, я перепрограммирую запросы на использование таблицы I, и у всех есть: sys.objects.

WITH object_rows AS ( SELECT object_id, ROW_NUMBER() OVER ( ORDER BY object_id) RN FROM sys.objects) SELECT object_id FROM object_rows WHERE RN > 1 SELECT object_id FROM (SELECT object_id, ROW_NUMBER() OVER ( ORDER BY object_id) RN FROM sys.objects) T WHERE RN > 1

Созданные планы запросов были точно такими же. Я бы ожидал, что во всех случаях оптимизатор запросов будет иметь тот же план, по крайней мере, при простой замене CTE встроенным представлением или наоборот.

Конечно, попробуйте свои собственные запросы в своей собственной системе, чтобы увидеть, есть ли разница.

Кроме того, row_number() в предложении where является общей ошибкой в ответах, заданных в . Logicaly row_number() недоступен до тех пор, пока не будет обработано предложение select. Люди забывают об этом, и когда они отвечают без проверки ответа, ответ иногда ошибочен. (Я обвинил себя в этом виновнике).

основанный на ответе OP на вопрос:

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

Разбитый запрос с использованием сортировки по разным столбцам с использованием ROW_NUMBER () OVER () в SQL Server 2005

~ Джозеф

«Метод 1» похож на запрос OP из связанного вопроса, а «метод 2» похож на запрос из выбранного ответа. Вам нужно было посмотреть на код, связанный в этом answer чтобы узнать, что действительно происходит, поскольку код в выбранном ответе был изменен, чтобы заставить его работать. Попробуй это:

ВЫВОД:

code-examples.net

mysql - Надежность и соответствие ANSI-SQL использованию переменных для эмуляции MS SQL Server ROW_NUMBER() в MySQL?

Как обсуждалось в других разделах этого форума (например, здесь), в MySQL можно использовать переменные для эмуляции функции row_number() over (partition by... order by...) в MS SQL Server. Например, такой код:

SELECT col1, col2, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS rowNum FROM Table1

могут быть реализованы в MySQL следующим образом:

SELECT @rowNum := CASE WHEN col1<>@col1 OR col2<>@col2 THEN 1 ELSE @rowNum+1 END rowNum , @col1 := col1 col1 , @col2 := col2 col2 FROM (SELECT @col1 := 'xxx', @col2 := 'yyy', @rowNum := 0) a, (SELECT col1, col2, col3 FROM Table1 ORDER BY col1, col2, col3 DESC) b

Хотя это довольно быстро (поскольку это позволяет избежать самостоятельного объединения), но я не решаюсь поставить это в производство, поскольку он делает подробные предположения о плане выполнения запроса, который может быть или не быть переносимым. В частности, он предполагает, что:

1) Строки таблицы 'b' обрабатываются в порядке сортировки внешним запросом,

2) Колонки внешнего запроса вычисляются слева направо (т.е. rowNum → col1 → col2.

Эти предположения, похоже, нарушают обычную абстракцию синтаксиса SQL, к которому я привык. Кроме того, этот запрос требует от программиста обеспечения того, чтобы начальные значения, которые он/она поставляет для col1 и col2, не существуют в таблице 1. Опять же, это плохой дизайн, на мой взгляд. Это приводит меня к связанному вопросу, является ли эта последняя форма (с использованием переменных) совместимой с ANSI SQL (92 или 99), или это функция MySQL? Что является определенным источником, чтобы решить, является ли данный синтаксис жалобой ANSI SQL или нет? Вы чувствовали бы себя достаточно комфортно, чтобы выпустить вторую версию?

qaru.site

Как работает ROWNUM в запросе страницы?

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

Почему (это была моя первая попытка, пока я не искал SO). Выберите * От кого, где rownum> 100 и rownum < 110; возвращает 0 строк?

Хорошее объяснение Thomas Kyte относительно ROWNUM и разбиение на страницы here.

Значение ROWNUM присваивается строке после того, как она проходит фазу предикат запроса, но перед запросом делает любой сортировки или агрегации.Кроме того, значение ROWNUM увеличивается только после того, как он назначен, поэтому следующий запрос никогда не будет возвращать строку:

select * from t where ROWNUM > 1;

Поскольку ROWNUM> 1 не верно для первой строки, ROWNUM не продвигается до 2 . Следовательно, никакое значения ROWNUM никогда не добирается, чтобы быть больше 1.

Почему нет простого способа сделать что-то вроде SELECT ... FROM ... WHERE ROWNUM МЕЖДУ LowerBound И UpperBound?

Да, есть. Начиная с Oracle 12c, вы можете использовать новую строку Top-n Row, ограничивающую. See my answer here.

Например, ниже запрос возвратит сотрудник между четвёртыми высоким до 7-высоких зарплат в порядке возрастания:

SQL> SELECT empno, sal 2 FROM emp 3 ORDER BY sal 4 OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY; EMPNO SAL ---------- ---------- 7654 1250 7934 1300 7844 1500 7499 1600 SQL>

Как избавиться от столбца г в результирующих значениях ?

Вместо select *, укажите необходимые имена столбцов во внешнем запросе. Для частого использования запроса создание представления - это простая одноразовая работа.

В качестве альтернативы, в SQL*Plus можно использовать NOPRINT команду. Он не отображает имя столбца, которое вы не хотите отображать. Однако он будет работать только в SQL * Plus.

Например,

COLUMN column_name NOPRINT

Например,

SQL> desc dept Name Null? Type ----------------------------------------- -------- ------------ DEPTNO NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) SQL> COLUMN dname NOPRINT SQL> COLUMN LOC NOPRINT SQL> SELECT * FROM dept; DEPTNO ---------- 10 20 30 40 SQL>

это Обеспечивают ли правильно пагинацию?

Да, если вы правильно пишете запрос разбивки на страницы.

Например,

SELECT val FROM (SELECT val, rownum AS rnum FROM (SELECT val FROM t ORDER BY val) WHERE rownum <= 8) WHERE rnum >= 5; VAL ---------- 3 3 4 4 4 rows selected. SQL>

Или использовать новую строку предельную функцию на 12с, как я показал выше.

Немного хороших примеров here.

stackoverrun.com

sql - Как ROWNUM работает в запросе страницы?

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

Почему (это была моя первая попытка, пока я не поискаю SO). Выберите * From Person Где rownum > 100 и rownum < 110; возвращает 0 строк?

Хорошее объяснение Томаса Кита относительно ROWNUM и разбивки на страницы здесь.

Значение ROWNUM присваивается строке после прохождения фазы предиката запроса, но до того, как запрос выполнит какую-либо сортировку или агрегацию. Кроме того, значение ROWNUM увеличивается только после того, как оно назначено, поэтому следующий запрос никогда не вернет строку:

select * from t where ROWNUM > 1;

Поскольку ROWNUM > 1 не относится к первой строке, ROWNUM не продвигается к 2. Следовательно, ни одно значение ROWNUM никогда не будет больше 1.

Почему нет простого способа сделать что-то вроде Select... FROM... WHERE rownum МЕЖДУ lowerBound AND upperBound?

Да, есть. Начиная с Oracle 12c, вы можете использовать новую верхнюю строку ограничения строки. См. мой ответ здесь.

Например, приведенный ниже запрос вернет сотрудников между 4-м высшим до 7-й высшей зарплаты в порядке возрастания:

SQL> SELECT empno, sal 2 FROM emp 3 ORDER BY sal 4 OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY; EMPNO SAL ---------- ---------- 7654 1250 7934 1300 7844 1500 7499 1600 SQL>

Как избавиться от столбца r в результирующих значениях?

Вместо select * перечислите необходимые имена столбцов во внешнем запросе. Для частого использования запроса создание представления - это простая одноразовая работа.

В качестве альтернативы, в SQL*Plus вы можете использовать команду NOPRINT. Он не отображает имя столбца, которое вы не хотите отображать. Однако он будет работать только в SQL * Plus.

Например,

COLUMN column_name NOPRINT

Например,

Обеспечивает ли он правильную разбивку на страницы?

Да, если вы правильно пишете запрос разбивки на страницы.

Например,

SELECT val FROM (SELECT val, rownum AS rnum FROM (SELECT val FROM t ORDER BY val) WHERE rownum <= 8) WHERE rnum >= 5; VAL ---------- 3 3 4 4 4 rows selected. SQL>

Или используйте новую функцию ограничения строк на 12c, как показано выше.

Несколько хороших примеров здесь.

qaru.site

sql - Получение значения предыдущей записи с использованием ROW_NUMBER() в SQL Server

Здесь запрос, который будет проходить только через dbo.Activity ONCE

SELECT H.CUSTOMER ,H.LEDGER ,H.ACCOUNT ,MAX(H.ACTIVITY_DATE) ACTIVITY_DATE ,SUM(CASE X.I WHEN 1 THEN AMOUNT ELSE -AMOUNT END) AMOUNT FROM (SELECT CUSTOMER ,LEDGER ,ACCOUNT ,ACTIVITY_DATE ,AMOUNT ,ROW_NUMBER() OVER (PARTITION BY CUSTOMER, LEDGER, ACCOUNT ORDER BY ACTIVITY_DATE DESC) AS ROW_NUMBER FROM dbo.ACTIVITY WITH (NOLOCK) ) H CROSS JOIN (select 1 union all select 2) X(I) WHERE ROW_NUMBER - X.I >= 0 GROUP BY H.CUSTOMER ,H.LEDGER ,H.ACCOUNT ,ROW_NUMBER - X.I;

И вот DDL/DML для некоторых данных, которые я использовал для тестирования

CREATE TABLE dbo.ACTIVITY(CUSTOMER int, LEDGER int, ACCOUNT int, ACTIVITY_DATE datetime, AMOUNT int) INSERT dbo.ACTIVITY select 1,2,3,GETDATE(),123 union all select 1,2,3,GETDATE()-1,16 union all select 1,2,3,GETDATE()-2,12 union all select 1,2,3,GETDATE()-3,1 union all select 4,5,6,GETDATE(),1000 union all select 4,5,6,GETDATE()-6,123 union all select 7,7,7,GETDATE(),99;

Альтернативы

Более традиционный подход с использованием подзапроса для получения предыдущей строки:

SELECT CUSTOMER, LEDGER, ACCOUNT, ACTIVITY_DATE, AMOUNT - ISNULL((SELECT TOP(1) I.AMOUNT FROM dbo.ACTIVITY I WHERE I.CUSTOMER = O.CUSTOMER AND I.LEDGER = O.LEDGER AND I.ACCOUNT = O.ACCOUNT AND I.ACTIVITY_DATE < O.ACTIVITY_DATE ORDER BY I.ACTIVITY_DATE DESC), 0) AMOUNT FROM dbo.ACTIVITY O ORDER BY CUSTOMER, LEDGER, ACCOUNT, ACTIVITY_DATE;

Или ROW_NUMBER() данные дважды и соедините между ними

SELECT A.CUSTOMER, A.LEDGER, A.ACCOUNT, A.ACTIVITY_DATE, A.AMOUNT - ISNULL(B.AMOUNT,0) AMOUNT FROM (SELECT *, RN=ROW_NUMBER() OVER (partition by CUSTOMER, LEDGER, ACCOUNT order by ACTIVITY_DATE ASC) FROM dbo.ACTIVITY) A LEFT JOIN (SELECT *, RN=ROW_NUMBER() OVER (partition by CUSTOMER, LEDGER, ACCOUNT order by ACTIVITY_DATE ASC) FROM dbo.ACTIVITY) B ON A.CUSTOMER = B.CUSTOMER AND A.LEDGER = B.LEDGER AND A.ACCOUNT = B.ACCOUNT AND B.RN = A.RN-1 -- prior record ORDER BY A.CUSTOMER, A.LEDGER, A.ACCOUNT, A.ACTIVITY_DATE;

qaru.site

Как использовать rownum Безопасный SQL

У меня есть таблица сотрудников в оракуле с именем, зарплатой и другими деталями.

Я пытаюсь получить вторую самую высокую зарплату, но не могу ее получить.

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

но поскольку я заменяю rownum=2 на rownum<2 он выдает выходные данные первых двух записей. Пожалуйста, объясните, почему rownum=2 не работает

Это будет работать:

выберите зарплату из (выберите зарплату, rownum как rn from (выберите зарплату из e_salary order по зарплате desc)) где rn = 2;

Почему это не работает:

При присвоении ROWNUM строке Oracle начинается с 1 и только увеличивает значение при выборе строки; то есть, когда выполняются все условия в предложении WHERE. Поскольку для нашего условия требуется, чтобы ROWNUM был больше 2, строки не выбраны, а ROWNUM никогда не увеличивается на 1.

Надеюсь, что сейчас ясно.

select ename ,sal ,rank() over (order by sal desc) ranking from emp;

Попробуй это.

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

http://www.oratable.com/nth-highest-salary-in-oracle/

Использование rownum – сложное дело. Самая безопасная ставка – использовать ее только тогда, когда вы хотите ограничить количество результатов, которые будут показаны. Например, rownum <2 или rownum <= 5.

Почему rownum = 2 не будет работать?

Читайте здесь – http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

Итак, вот как оракул выполняет запрос

Предложение FROM / WHERE идет первым.
ROWNUM назначается и увеличивается до каждой выходной строки из предложения FROM / WHERE.
Применяется SELECT.
GROUP BY применяется.
HAVING применяется.
Применяется ORDER BY.

Предложение rownum <= 2 преобразуется в

ROWNUM = 1 for x in ( select * from emp ) loop exit when NOT(ROWNUM <= 2) OUTPUT record to temp ROWNUM = ROWNUM+1 end loop SORT TEMP

если вы измените exit, когда NOT (ROWNUM <= 2) с rownnum = 2, вы увидите, что он потерпит неудачу в первом запуске

Так что, если я не могу использовать rownum, что я могу использовать. Попробуйте использовать row_number () http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm

Он работает примерно так

SELECT last_name FROM (SELECT last_name, ROW_NUMBER() OVER (ORDER BY last_name) R FROM employees) WHERE R BETWEEN 51 and 100;

rownum в состоянии перестает оценивать первый раз, когда он терпит неудачу. В первом rownum равен 1, поэтому он терпит неудачу в rownum = 2 теста rownum = 2 и перестает пытаться. Здесь есть отличный пост.

Чтобы получить вторую по величине зарплату, используйте аналитическую функцию DENSE_RANK Oracle:

SELECT DISTINCT Salary FROM ( SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank FROM e_salary) WHERE SalaryRank = 2

Обратите внимание, что если есть связь за секунду, запрос может вернуть более одного значения. Вот почему внешний SELECT является SELECT DISTINCT .

Сначала вы должны понять, что такое rownum . Позволь мне привести пример,

you want to get data with a filter and rownum=2, first Oracle executes the sql with filter and get the first record, give it the rownum 1, and then compare it the rownum filter rownum=2, which doesn't match, so discard record, then get second record, give it rownum=1(if the first record is matched then the rownum will be 2) too, then do the compare............

Поэтому вы можете найти причину.

Без использования команды rownum вы можете получить вторую самую высокую зарплату, используя этот запрос:

select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee )

или,

select MAX(Salary) from Employee WHERE Salary <> (select MAX(Salary) from Employee )

запрос для n-го наивысшего значения:

SELECT * FROM Employee Emp1 WHERE (N-1) = (SELECT COUNT(DISTINCT(Emp2.Salary))FROM Employee Emp2 WHERE Emp2.Salary > Emp1.Salary)

Из того, что я понимаю, rownum выводит строки в результирующем наборе.

Итак, в вашем примере:

выберите * из таблицы1, где rownum = 2

Сколько строк будет в результирующем наборе? Таким образом, какой rownum будет присвоен такой строке? Вы видите, почему результат не возвращается?

В общем, вам следует избегать полагаться на rownum или на любые функции, которые подразумевают заказ на результат. Постарайтесь подумать о работе со всем набором результатов.

С учетом сказанного, я считаю, что следующее будет работать:

выберите * from (выберите rownum как rn, table1. * из таблицы 1) как t, где t.rn = 2

Потому что в этом случае вы нумеруете строки внутри подзапроса.

sql.fliplinux.com