如何在sql server中获取上一行数据 [英] How to get previous row data in sql server

问题描述

我想获取上一行的数据.我使用了 LAG 功能，但没有得到预期的结果.

I would like to get the data from previous row. I have used LAG function but did not get the expected result.

表格:-

col1  col2  col3
ABCD    1   Y
ABCD    2   N
ABCD    3   N
EFGH    4   N
EFGH    5   Y
EFGH    6   N
XXXX    7   Y

预期结果

col1 col2 col3  col4
ABCD    1   A   NULL
ABCD    2   B   A
ABCD    3   C   B
EFGH    4   A   NULL
EFGH    5   B   A
EFGH    6   E   B
XXXX    7   F   NULL

Col4 应保存按 Col1 中的值分组的前一行的数据.请让我知道如何实现.

Col4 should hold the data from previous row grouping by the value in Col1. Please let me know how can this be achieved.

推荐答案

使用lag()函数

select *, lag(col3) over (partition by col1 order by col2) as col4
from table t;

但是，如果您的 SQL 没有 LAG()

However You can also use subquery if your SQL doesn't have LAG()

select *,   
        (select top 1 col3
         from table
         where col1 = t.col1 and col2 < t.col2
         order by col2 desc
        ) as col4
from table t;

这篇关于如何在sql server中获取上一行数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！