有没有方法访问select语句中的“上一行”值?

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

如果需要断开连接,可以根据需要向ORDERBY添加尽可能多的列。

使用 lag 功能:

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

用于ID的序列可以跳过值,因此ID-1并不总是有效。

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

Oracle、PostgreSQL、SQL Server和更多的RDBMS引擎都具有称为 LAG 和 LEAD 就是这样。

在2012年之前的SQL Server中,您需要执行以下操作:

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) 将大大改进此查询。

将表左联接到其自身,并计算出联接条件,以便表的联接版本中匹配的行是前一行,用于您的特定“上一行”定义。

更新:首先,我想您应该保留所有行,对于没有前一行的条件为空。再次阅读它,您只需要剔除行,所以您应该是一个内部联接而不是一个左联接。

更新:

较新版本的SQL Server还具有延迟和超前窗口功能,也可以用于此功能。

所选答案仅在序列中没有间隙时有效。但是,如果您使用的是自动生成的ID,那么由于回滚的插入,序列中可能存在间隙。

如果有差距,这个方法应该有效

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

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