是否有办法访问 SELECT 语句中的“前一行”值？

我需要计算表的两行之间列的差值。有没有什么办法可以直接在 SQL 中完成这项工作？我用的是2008年的 Microsoft SQL Server。

我在找这样的东西:

SELECT value - (previous.value) FROM table

假设“前一个”变量引用最新选定的行。当然，使用这样的选择，我将在一个有 n 行的表中选择 n-1行，这不是一个可能，实际上正是我所需要的。

这可能吗？

442083 次浏览
小开
最佳答案

SQL 没有内置的顺序概念，因此需要按某个列进行排序，这样才有意义。就像这样:

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

如果你知道如何排序，但不知道如何得到给定当前值的前一个值(例如，你想按字母顺序排序) ，那么我不知道在标准 SQL 中有什么方法可以做到这一点，但大多数 SQL 实现都会有扩展来做到这一点。

下面是 SQL 服务器的一种工作方式，如果您可以对行进行排序，使得每一行都是不同的:

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 添加尽可能多的列。

小开

将表单连接到它自己，并计算出连接条件，以便在表的连接版本中匹配的行位于前一行，用于您对“前一行”的特定定义。

更新: 起初我认为应该保留所有行，在没有前一行的情况下使用 NULL。再次读取它时，您只需要剔除该行，因此应该使用内部联接而不是左联接。

更新:

较新版本的 SqlServer 还具有 LAG 和 LEADWindowingfunction，也可以用于此目的。

小开

Oracle、 PostgreSQL、 SQLServer 和许多其他 RDBMS 引擎都具有称为 LAG和 LEAD的分析函数，这些函数正是做这件事的。

在2012年之前的 SQLServer 中，您需要执行以下操作:

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)上有一个索引将极大地改进这个查询。

小开

所选择的答案只有在序列中没有间隙时才有效。但是，如果您使用的是自动生成的 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
小开 
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
小开 
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
小开

使用 滞后函数:

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

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

小开

在 SQL 查询中引用前一行的另一种方法是使用 递归公共表表达式(CTE):

CREATE TABLE t (counter INTEGER);


INSERT INTO t VALUES (1),(2),(3),(4),(5);


WITH cte(counter, previous, difference) AS (
-- Anchor query
SELECT MIN(counter), 0, MIN(counter)
FROM t
UNION ALL
-- Recursive query
SELECT t.counter, cte.counter, t.counter - cte.counter
FROM t JOIN cte ON cte.counter = t.counter - 1
)
SELECT counter, previous, difference
FROM cte
ORDER BY counter;

结果:

计数器 上一页 不同
1 0 1
2 1 1
3 2 1
4 3 1
5 4 1

锚查询生成公共表表达式 cte的第一行，其中将表 t的第一行中的 cte.counter设置为列 t.counter，将 cte.previous设置为0，将 cte.difference设置为 t.counter的第一行。

递归查询将公共表表达式 cte的每一行连接到表 t的前一行。在递归查询中，cte.counter在表 t的每一行中引用 t.counter，cte.previous在 cte的前一行中引用 cte.counter，而 t.counter - cte.counter则引用这两列之间的差异。

注意，递归 CTE 比 LAG 和 LEAD 函数更灵活，因为行可以引用前一行的任意结果。(递归函数或过程是这样一个流程的输入是该流程的前一次迭代的输出，除了第一个输入是常量。)

我在 SQLite Online测试了这个查询。

小开

可以使用以下函数获取当前行值和上一行值:

SELECT value,
min(value) over (order by id rows between 1 preceding and 1
preceding) as value_prev
FROM table

然后你可以从中选择 value - value_prev并得到你的答案


提交答案