SQL中的嵌套CASE [英] Nested CASE in SQL
问题描述
TABLE1:
ARTIKEL SUPPLIERID SALE_SUM_PIECES
TV SONY 7
TABLE2:
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
仅当值TABLE1."SALE_SUM_PIECES"小于TABLE2中的"PIECES"的总和时,我才需要在TABLE2."PIECES"
上减去值X=23
.例如:TABLE1."SALE_SUM_PIECES"
的值是7
.现在,我需要检查TABLE2."PIECES"
的总和7 goes less than
在哪一行.在下面的示例中,TABLE2中的第一行无效,因为7大于6.但是TABLE2中的第二行有效,因为SUM表2中第1行和第2行的部分"的OF大于6,即6 + 10 = 16.因此,我需要从第二行减去TABLE2
中的后续行减去X=23
的值.
我的查询如下:
I need to subtract value X=23
on TABLE2."PIECES"
, only when the value TABLE1."SALE_SUM_PIECES" is less than the SUM of "PIECES" in TABLE2. For example: the value of TABLE1."SALE_SUM_PIECES"
is 7
. NOw I need to check at which row the value 7 goes less than
the SUM of TABLE2."PIECES"
.In the below example the first row in TABLE2 is not valid because 7 is greater than 6. But the second row in TABLE2 is valid since the SUM OF "PIECES" from row1 and row2 in TABLE2 i.e 6+10=16 is greater than 7. So, I need to subtract value of X=23
from the second row to the following rows in TABLE2
.
The query I have is as follows:
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
)
当我执行上面的查询时,我得到的结果如下:
When I execute the above query I get the result as follows:
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
我希望"X = 23"的减法从表2中条件TABLE1."SALE_SUM_PIECES" < TABLE2."PIECES"
的行开始,即从行2开始.有什么建议?
I want the subtraction of ´X=23´ to start from the row in TABLE2 where the condition TABLE1."SALE_SUM_PIECES" < TABLE2."PIECES"
i.e from row2. Any suggestions?
谢谢.
推荐答案
以下解决方案可提供所需的结果.参见 SqlFiddle
The below solution gives the desired results. See 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
这篇关于SQL中的嵌套CASE的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!