sql - SQL 中的嵌套 CASE

Question

表1:

ARTIKEL      SUPPLIERID   SALE_SUM_PIECES   
TV            SONY            7            

表2:

 ROW_ID     ARTIKEL      SUPPLIERID     PIECES    
    1           TV            SONY       6       
    2           TV            SONY       10      
    3           TV            SONY       6      
    4           TV            SONY       14       
    5           TV            SONY       18      
    6           TV            SONY       4  

我需要减去值X=23上TABLE2."PIECES" ，仅当值 TABLE1."SALE_SUM_PIECES"小于 TABLE2 中的 "PIECES"的 SUM 时。例如:TABLE1."SALE_SUM_PIECES"的值是 7 。现在我需要检查哪一行的值 7 goes less than TABLE2."PIECES" 的总和在下面的示例中，TABLE2 中的第一行无效，因为 7 大于 6。但是 TABLE2 中的第二行有效，因为 TABLE2 中 row1 和 row2 的“PIECES”之和，即 6+10=16 大于7.所以，我需要减去X=23的值从 TABLE2 中的第二行到以下行。我的查询如下:

SELECT "SUPPLIERID", "ARTIKEL",
    (case when ( cumulativesum - (select "SALE_SUM_PIECES" from  T1 where T1."SUPPLIERID"=T2."SUPPLIERID" and T1."ARTIKEL" = T2."ARTIKEL" )) <= 0
        then NULL
        when ( cumulativesum - (select "SALE_SUM_PIECES" from  TABLE1 T1 where T1."SUPPLIERID"=T2."SUPPLIERID" and T1."ARTIKEL" = T2."ARTIKEL" )) > 0 
        then
            (case when @x - cumulativesum <= 0 and @x - (cumulativesum -PIECES) > 0
                 then 0
                 when @x - "cumulativesum" <= 0
                 then NULL
                 else @x - "cumulativesum"
            end) as "VALUE_DRILL_DOWN"
    from (SELECT T1."ARTIKEL", T1."SUPPLIERID", T1.PIECES
             (select sum("PIECES")
              from EXAMPLE_TABLE T2
              where T2."ROW_ID" <= T1."ROW_ID" and T2."SUPPLIERID" = T1."SUPPLIERID" and T2."ARTIKEL"=T1."ARTIKEL"
             ) as "cumulativesum"
     from EXAMPLE_TABLE T1
     ) 

当我执行上述查询时，我得到的结果如下:

 ROW_ID     ARTIKEL      SUPPLIERID    PIECES     VALUE_DRILL_DOWN
    1           TV            SONY      6            NULL
    2           TV            SONY      10           7
    3           TV            SONY      6            1
    4           TV            SONY      14           0
    5           TV            SONY      18           Null
    6           TV            SONY       4           Null 

但我预计结果如下:

   ROW_ID       ARTIKEL      SUPPLIERID    PIECES    VALUE_DRILL_DOWN
    1           TV            SONY           6           NULL
    2           TV            SONY          10           13
    3           TV            SONY          6            7
    4           TV            SONY          14           0
    5           TV            SONY          18           Null
    6           TV            SONY          4            Null

我希望从 TABLE2 中条件 TABLE1."SALE_SUM_PIECES" < TABLE2."PIECES" 的行开始减去“X=23”即来自 row2。有什么建议么？

提前致谢。

Answer 1

以下解决方案给出了所需的结果。请参阅SqlFiddle

1   TV  SONY    922 6   110 2.50    NULL
2   TV  SONY    922 10  80  1.00    13
3   TV  SONY    922 6   65  1.50    7
4   TV  SONY    922 14  95  1.50    0
5   TV  SONY    922 18  95  1.50    NULL
6   TV  SONY    922 4   95  1.50    NULL



DECLARE @x INT = 23

; WITH cte AS
(
    SELECT t2.*, t1.SALE_SUM_PIECES,
            CASE 
                WHEN SUM(t2.PIECES) OVER (PARTITION BY t2.ARTIKEL, t2.SUPPLIERID ORDER BY ROW_ID) < t1.SALE_SUM_PIECES THEN 'None'
                ELSE t2.ARTIKEL + t2.SUPPLIERID
            END AS GroupId
    FROM @Table2 t2
    JOIN @Table1 t1 ON t2.ARTIKEL = t1.ARTIKEL AND t2.SUPPLIERID = t1.SUPPLIERID
),
cumulative AS
(
    SELECT *, SUM(PIECES) OVER (PARTITION BY GroupId ORDER BY ROW_ID) AS CumulativeSum
    FROM cte
)
SELECT ROW_ID, ARTIKEL, SUPPLIERID, ORGID, PIECES, COSTPRICE, DISCOUNT, 
        CASE
            WHEN CumulativeSum < SALE_SUM_PIECES THEN NULL
            WHEN @x - CumulativeSum <= 0 AND @x - (CumulativeSum - PIECES) > 0 THEN 0
            WHEN @x - CumulativeSum <= 0 THEN NULL
            ELSE @x - CumulativeSum
        END AS VALUE_DRILL_DOWN
FROM cumulative