时间戳

首页 > 解决方案 > 试图让分区中的滞后以显示以前的值

sql-server - 试图让分区中的滞后以显示以前的值

问题描述

我试图在某个分区中获取先前的值。

我试图做一个 Lag Partition over 子句，它仍然给我 NULL。

标签： sql-server

解决方案

LAG 是 MS SQl Server 2012 中引入的出色窗口功能。 https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-2017

    DECLARE @Table TABLE (BusinessEntityID int, SalesYear int,   SalesQuota decimal);
    INSERT INTO @Table  (BusinessEntityID, SalesYear, SalesQuota)
    VALUES
     (275,2005,367000)
    ,(275,2005,556000)
    ,(275,2006,502000)
    ,(275,2006,550000)
    ,(275,2006,1429000)
    ,(275,2006,1324000);

    SELECT BusinessEntityID, SalesYear AS SalesYear, SalesQuota AS CurrentQuota,   
           LAG(SalesQuota, 1,0) OVER (ORDER BY SalesYear) AS PreviousQuota  
    FROM @Table
    WHERE BusinessEntityID = 275 and SalesYear IN ('2005','2006');

执行查询的结果：

   BusinessEntityID SalesYear   CurrentQuota    PreviousQuota
   275                  2005    367000                0
   275                  2005    556000                367000
   275                  2006    502000                556000
   275                  2006    550000                502000
   275                  2006    1429000               550000
   275                  2006    1324000               1429000

推荐阅读