代码之家 › 专栏 › 技术社区 › mitchmitch24

SSMS(SQL)无法识别以下情况。。。跳到最后0

dbo case-when sql-server

mitchmitch24 · 技术社区 · 8 年前

例如,这将返回订单号55和Calc\U ORDER\U NUMBER\U LAG 55,Calc\U Qty\U Changed列不会给我2,即使它们相等。。。

USE MfgMetrics

SELECT 
    [ORDER NUMBER], 
    [Calc_ORDER_NUMBER_LAG] = LAG([ORDER NUMBER],1,0) 
                                 OVER (Order By [ORDER NUMBER],[FileDate]), 
                                 --Order Number and File Date 
    [Order_Quantity], 
    [Calc_Order_Quantity_LAG] = LAG([Order_Quantity],1,0) 
                                   OVER (Order By [ORDER NUMBER], [FileDate]),
    [Calc_Qty_Changed] = 
        (CASE 
            WHEN [ORDER NUMBER] = [Calc_ORDER_NUMBER_LAG] THEN 2
            WHEN [ORDER NUMBER] != [Calc_ORDER_NUMBER_LAG] AND 
                        [Order_Quantity] != [Calc_Order_Quantity_LAG] AND 
                        [ACTUAL START DATE] != 0 AND 
                        [FileDate] >= [ACTUAL START DATE] THEN 1
            ELSE 0
        END)
FROM 
    dbo.Table_II

为什么每次都会跳到ELSE 0,即使在满足其他条件的情况下也是如此?

2 回复 | 直到 8 年前

McNets 8 年前

您应该在CASE语句中重写LAG()函数。

create table table_II([ORDER NUMBER] int, [Order_Quantity] int, [FileDate] date);
insert into table_II values
(1, 10, '20180101'),
(2, 20, '20180102'),
(2, 30, '20180103');
GO

3 rows affected

SELECT 
    [ORDER NUMBER], 
    [Calc_ORDER_NUMBER_LAG] = LAG([ORDER NUMBER]) OVER (Order By [ORDER NUMBER], [FileDate]), 
    [Order_Quantity], 
    [Calc_Order_Quantity_LAG] = LAG([Order_Quantity]) OVER (Order By [ORDER NUMBER], [FileDate]),
    [Calc_Qty_Changed] = 
        (CASE 
            WHEN [ORDER NUMBER] = LAG([ORDER NUMBER]) OVER (Order By [ORDER NUMBER], [FileDate]) 
                 THEN 2
            WHEN [ORDER NUMBER] != LAG([ORDER NUMBER]) OVER (Order By [ORDER NUMBER], [FileDate]) 
                 AND [Order_Quantity] != LAG([Order_Quantity]) OVER (Order By [ORDER NUMBER], [FileDate]) 
                 --AND [ACTUAL START DATE] != 0 
                 --AND [FileDate] >= [ACTUAL START DATE] 
                 THEN 1
            ELSE 0
        END)
FROM 
    dbo.Table_II
GO

ORDER NUMBER | Calc_ORDER_NUMBER_LAG | Order_Quantity | Calc_Order_Quantity_LAG | Calc_Qty_Changed
-----------: | --------------------: | -------------: | ----------------------: | ---------------:
           1 |                  null |             10 |                    null |                0
           2 |                     1 |             20 |                      10 |                1
           2 |                     2 |             30 |                      20 |                2

D小提琴 here

user3532232 8 年前

我猜,因为我不知道数据是什么样子,你应该这样做:

USE MfgMetrics 
SELECT [ORDER NUMBER],
[Calc_ORDER_NUMBER_LAG] = LAG([ORDER NUMBER],1,0) OVER (Order By [ORDER NUMBER],[FileDate]), --Order Number and File Date
[Order_Quantity],
[Calc_Order_Quantity_LAG] = LAG([Order_Quantity],1,0) OVER (Order By [ORDER NUMBER], [FileDate]),
[Calc_Qty_Changed] = (CASE 
    WHEN [ORDER NUMBER]=LAG([ORDER NUMBER],1,0) OVER (Order By [ORDER NUMBER],[FileDate]) THEN 2 
    WHEN [ORDER NUMBER]!=LAG([ORDER NUMBER],1,0) OVER (Order By [ORDER NUMBER],[FileDate]) 
        AND [Order_Quantity] != LAG([Order_Quantity],1,0) OVER (Order By [ORDER NUMBER], [FileDate]) AND [ACTUAL START DATE] != 0 AND [FileDate] >= [ACTUAL START DATE] 
    THEN 1 
    ELSE 0 END) 
FROM dbo.Table_II