SQL 从下一行获取值

Question

我正在寻找一种 SQL 方法来获取下一行的值。

我的数据如下:

CUST    PROD     From_Qty    Disc_Pct
23      Brush    1           0
23      Brush    13          1
23      Brush    52          4
77      Paint    1           0
77      Paint    22          7

我最终需要的是这个，(我想创建 To_Qty 行):

CUST    PROD     From_Qty    To_Qty    Disc_Pct
23      Brush    1           12        0
23      Brush    13          51        1              #13 is 12+1
23      Brush    52          99999     4              #52 is 51+1
77      Paint    1           21        0              #1  is 99999+1
77      Paint    22          99999     7              #22 is 21+1

我有 100K+ 行要执行此操作，它必须是 SQL，因为我的 ETL 应用程序允许 SQL 但不允许存储过程等。

如何从下一行获取值以便创建 To_Qty？

Answer 1

SELECT  *,
        LEAD([From_Qty], 1, 100000) OVER (PARTITION BY [CUST] ORDER BY [From_Qty]) - 1 AS To_Qty
FROM    myTable

LEAD() 将根据 [From_Qty] 的顺序为您获取下一个值。您使用 PARTITION BY [CUST]当 [Cust] 改变值时重置

或者您可以使用 CTE 和 Row_Number。

WITH cte AS 
(
    SELECT  *,
            ROW_NUMBER() OVER (PARTITION BY [CUST] ORDER BY [From_Qty]) Rn
    FROM    myTable
)
SELECT  t1.*,
        ISNULL(t2.From_Qty - 1, 99999) To_Qty
FROM    cte t1
        LEFT JOIN cte t2 ON t1.Cust = t2.Cust AND t1.Rn + 1 = t2.Rn