Есть ли способ получить доступ к значению "предыдущая строка" в инструкции SELECT?

Мне нужно вычислить разницу колонны между двумя строками таблицы. Есть ли способ сделать это непосредственно в SQL? Я использую Microsoft SQL Server 2008.

Я ищу что-то вроде этого:

SELECT value - (previous.value) FROM table

представив, что" предыдущая " переменная ссылается на последнюю выбранную строку. Конечно, с таким выбором я получу n-1 строк, выбранных в таблице с n строками, это не вероятно, на самом деле именно то, что я необходимость.

возможно ли каким-то образом?

7 65
sql sql-server sql-server-2008

7 ответов:

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

select t1.value - t2.value from table t1, table t2 
where t1.primaryKey = t2.primaryKey - 1

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

вот способ для SQL server, который работает, если вы можете заказать строки таким образом, что каждый один из них отличается:

select  rank() OVER (ORDER BY id) as 'Rank', value into temp1 from t

select t1.value - t2.value from temp1 t1, temp1 t2 
where t1.Rank = t2.Rank - 1

drop table temp1

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

46

использовать ЛАГ:

SELECT value - lag(value) OVER (ORDER BY Id) FROM table

последовательности, используемые для идентификаторов, могут пропускать значения, поэтому Id-1 не всегда работает.

32

Oracle, PostgreSQL, SQL Server и многие другие движки СУБД имеют аналитические функции под названием LAG и LEAD которые делают именно это.

в SQL Server до 2012 года вам нужно будет сделать следующее:

SELECT  value - (
        SELECT  TOP 1 value
        FROM    mytable m2
        WHERE   m2.col1 < m1.col1 OR (m2.col1 = m1.col1 AND m2.pk < m1.pk)
        ORDER BY 
                col1, pk
        )
FROM mytable m1
ORDER BY
      col1, pk

, где COL1 - это столбец, который вы заказываете по.

имея индекс на (COL1, PK) значительно улучшит этот запрос.

25
WITH CTE AS (
  SELECT
    rownum = ROW_NUMBER() OVER (ORDER BY columns_to_order_by),
    value
  FROM table
)
SELECT
  curr.value - prev.value
FROM CTE cur
INNER JOIN CTE prev on prev.rownum = cur.rownum - 1
24

слева присоедините таблицу к себе, при этом условие соединения разработано таким образом, что строка, сопоставленная в объединенной версии таблицы, является одной строкой предыдущей, для вашего конкретного определения "предыдущего".

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

обновление:

более новые версии Sql Server также имеют функции LAG и LEAD Windowing, которые также могут быть использованы для этого.

8

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

этот метод должен работать, если у вас есть пробелы

declare @temp (value int, primaryKey int, tempid int identity)
insert value, primarykey from mytable order by  primarykey

select t1.value - t2.value from @temp  t1
join @temp  t2 
on t1.tempid = t2.tempid - 1
2
select t2.col from (
select col,MAX(ID) id from 
(
select ROW_NUMBER() over(PARTITION by col order by col) id ,col from testtab t1) as t1
group by col) as t2
2