SQL Server 2008 R2 - 计算引用上一行

Question

我希望设计一个查询，其中计算将单元格中的值与同一列中前一个单元格中的值进行比较，另一个计算使用以前的计算结果来引用前一个单元格进行计算。数据将按以下示例图像中的以下顺序排列 - Material 、工厂和年周

Sample Data Image

从下图中，蓝色列标题代表原始数据，黄色列标题代表计算行。

计算行的公式如下:

第一的？ = IF(AND(B2=B1,C2=C1),"否","第一个")

实际可用 = IF(F2="First",D2-E2,G1-E2)

根据我的调查，LEAD 和 LAG 函数可以帮助实现这一点，但由于我仅限于 SQL Server 2008 R2，因此我无法使用它们。请找到创建表的查询。

    CREATE TABLE ZDAYS ([YearWeek] VARCHAR(7), 
    [Material] VARCHAR(3), [Plant] VARCHAR(3),
    [Inventory] INT, [Demand] INT)

    INSERT INTO ZDAYS VALUES ('2015-42', 'ABC', '101', 20, 5)
    INSERT INTO ZDAYS VALUES ('2015-43', 'ABC', '101', 20, 3)
    INSERT INTO ZDAYS VALUES ('2015-44', 'ABC', '101', 20, 2)
    INSERT INTO ZDAYS VALUES ('2015-42', 'ABC', '201', 30, 10)
    INSERT INTO ZDAYS VALUES ('2015-43', 'ABC', '201', 30, 8)
    INSERT INTO ZDAYS VALUES ('2015-44', 'ABC', '201', 30, 4)
    INSERT INTO ZDAYS VALUES ('2015-42', 'XYZ', '101', 10, 3)
    INSERT INTO ZDAYS VALUES ('2015-43', 'XYZ', '101', 10, 2)
    INSERT INTO ZDAYS VALUES ('2015-44', 'XYZ', '201', 20, 4)

最终数据输出

 YearWeek   Material    Plant   Inventory   Demand  First?  Actual Available
 2015-42    ABC          101        20        5     First         15             
 2015-43    ABC          101        20        3     No            12             
 2015-44    ABC          101        20        2     No            10
 2015-42    ABC          201        30        10    First         20
 2015-43    ABC          201        30        8     No            12
 2015-44    ABC          201        30        4     No            8
 2015-42    XYZ          101        10        3     First         7
 2015-43    XYZ          101        10        2     No            5
 2015-44    XYZ          201        20        4     First         16

Answer 1

改编自答案“无连接和无分析函数”:

http://blog.sqlauthority.com/2011/11/24/sql-server-solution-to-puzzle-simulate-lead-and-lag-without-using-sql-server-2012-analytic-function/

declare  @ZDAYS TABLE ([YearWeek] VARCHAR(7), 
    [Material] VARCHAR(3), [Plant] VARCHAR(3),
    [Inventory] INT, [Demand] INT)

    INSERT INTO @ZDAYS VALUES ('2015-42', 'ABC', '101', 20, 5)
    INSERT INTO @ZDAYS VALUES ('2015-43', 'ABC', '101', 20, 3)
    INSERT INTO @ZDAYS VALUES ('2015-44', 'ABC', '101', 20, 2)
    INSERT INTO @ZDAYS VALUES ('2015-42', 'ABC', '201', 30, 10)
    INSERT INTO @ZDAYS VALUES ('2015-43', 'ABC', '201', 30, 8)
    INSERT INTO @ZDAYS VALUES ('2015-44', 'ABC', '201', 30, 4)
    INSERT INTO @ZDAYS VALUES ('2015-42', 'XYZ', '101', 10, 3)
    INSERT INTO @ZDAYS VALUES ('2015-43', 'XYZ', '101', 10, 2)
    INSERT INTO @ZDAYS VALUES ('2015-44', 'XYZ', '201', 20, 4);

WITH T1 (RowNum, UniqueID, YearWeek, Material, Plant, Inventory, Demand)
AS
    (SELECT Row_Number() OVER(ORDER BY z.Material,z.Plant,z.YearWeek ) N
        ,1.0 + floor(10000 * RAND(convert(varbinary, newid()))) as UniqueID 
        ,z.YearWeek
        ,z.Material
        ,z.Plant
        ,z.Inventory
        ,z.Demand
        FROM @ZDAYS z
    )
select T1.*,
CASE WHEN RowNum%2=1 THEN MAX(CASE WHEN RowNum%2=0 THEN UniqueID END) OVER (Partition BY (RowNum+1)/2) ELSE MAX(CASE WHEN RowNum%2=1 THEN UniqueID END) OVER (Partition BY RowNum/2) END LeadValUniqueID,
CASE WHEN RowNum%2=1 THEN MAX(CASE WHEN RowNum%2=0 THEN UniqueID END) OVER (Partition BY RowNum/2) ELSE MAX(CASE WHEN RowNum%2=1 THEN UniqueID END) OVER (Partition BY (RowNum+1)/2) END LagValUniqueID
FROM T1
ORDER BY T1.Material, T1.Plant, T1.YearWeek
GO

或者，相同的逻辑是将 Lead() 和 Lag() 行连接起来进行识别，而不是生成唯一的 id。

declare  @ZDAYS TABLE ([YearWeek] VARCHAR(7), 
    [Material] VARCHAR(3), [Plant] VARCHAR(3),
    [Inventory] INT, [Demand] INT)

    INSERT INTO @ZDAYS VALUES ('2015-42', 'ABC', '101', 20, 5)
    INSERT INTO @ZDAYS VALUES ('2015-43', 'ABC', '101', 20, 3)
    INSERT INTO @ZDAYS VALUES ('2015-44', 'ABC', '101', 20, 2)
    INSERT INTO @ZDAYS VALUES ('2015-42', 'ABC', '201', 30, 10)
    INSERT INTO @ZDAYS VALUES ('2015-43', 'ABC', '201', 30, 8)
    INSERT INTO @ZDAYS VALUES ('2015-44', 'ABC', '201', 30, 4)
    INSERT INTO @ZDAYS VALUES ('2015-42', 'XYZ', '101', 10, 3)
    INSERT INTO @ZDAYS VALUES ('2015-43', 'XYZ', '101', 10, 2)
    INSERT INTO @ZDAYS VALUES ('2015-44', 'XYZ', '201', 20, 4);

-- LAG and LEAD result rows concatenated
WITH T1 (RowNum,  YearWeek, Material, Plant, Inventory, Demand)
AS
    (SELECT Row_Number() OVER(ORDER BY z.Material,z.Plant,z.YearWeek ) N
        ,z.YearWeek
        ,z.Material
        ,z.Plant
        ,z.Inventory
        ,z.Demand
        FROM @ZDAYS z
    )
select T1.*,
CASE WHEN RowNum%2=1 THEN MAX(CASE WHEN RowNum%2=0 THEN YearWeek + ' ' + Material + ' ' + Plant + ' ' + cast(Inventory as varchar(5)) + ' ' + cast(Demand as varchar(5)) END) OVER (Partition BY (RowNum+1)/2) ELSE MAX(CASE WHEN RowNum%2=1 THEN YearWeek + ' ' + Material + ' ' + Plant + ' ' + cast(Inventory as varchar(5)) + ' ' + cast(Demand as varchar(5)) END) OVER (Partition BY RowNum/2) END LeadRowValues,
CASE WHEN RowNum%2=1 THEN MAX(CASE WHEN RowNum%2=0 THEN YearWeek + ' ' + Material + ' ' + Plant + ' ' + cast(Inventory as varchar(5)) + ' ' + cast(Demand as varchar(5)) END) OVER (Partition BY RowNum/2) ELSE MAX(CASE WHEN RowNum%2=1 THEN YearWeek + ' ' + Material + ' ' + Plant + ' ' + cast(Inventory as varchar(5)) + ' ' + cast(Demand as varchar(5)) END) OVER (Partition BY (RowNum+1)/2) END LagRowValues
FROM T1
ORDER BY T1.Material, T1.Plant, T1.YearWeek
GO