如何根据条件从前一行中添加和减去值 [英] How to add and subtract value from previous rows based on condition

问题描述

我有一个包含值的表格

Slno Type     Amount        
 1    P         40           
 2    C         20           
 3    P         45           
 4    P         20           
 5    C         10   

我想获取 RESULT 列的值.

I want to get values for RESULT column.

   Type      Amount       RESULT 
    P         40           40
    C         20           20
    P         45           65
    P         20           85
    C         10           75

如果 Type 是 C，则从先前的值中减去值，否则，如果 Type 是 P，那么值会被添加到之前的值上.

If Type is C then value gets subtracted from previous value, else if Type is P then value gets added to previous values.

这是我试过的:

;WITH FINALMIDRESULT 
     AS (SELECT Type, 
                Value1, 
                Row_number() 
                  OVER( 
                    ORDER BY Slno ASC) rownum 
         FROM   #midRes) 
SELECT C1.Type, 
       C1.Value1, 
       CASE 
         WHEN C1.Type = 'C' THEN (SELECT Sum(Amount) 
                                  FROM   FINALMIDRESULT c2 
                                  WHERE  c2.rownum <= C1.rownum) 
         ELSE (SELECT Sum(Amount) - Sum(Amount) 
               FROM   FINALMIDRESULT c2 
               WHERE  c2.rownum <= C1.rownum) 
       END AS RESULT 
FROM   FINALMIDRESULT C1 

这是我得到的结果

  Type      Amount       RESULT 
    P         40           0
    C         20           60
    P         45           0
    P         20           0
    C         10           135

推荐答案

您需要实现一个 seft INNER JOIN 来对 Slno 小于当前值的所有值求和，如下所示:

You need to implement a seft INNER JOIN to sum all values with Slno less than the current value, like below:

;WITH   OriginalData    AS
(       SELECT  *
        FROM
        (       VALUES
                (1, 'P', 40),
                (2, 'C', 20),
                (3, 'P', 45),
                (4, 'P', 20),
                (5, 'C', 10)
        )       AS Temp(Slno, Type, Amount)
)
SELECT      [Current].Type, [Current].Amount,
            ISNULL(SUM(
            CASE    WHEN  [Previous].Type = 'P'
                    THEN +[Previous].Amount
                    ELSE -[Previous].Amount
            END),0) + 
            CASE    WHEN  [Current].Type = 'P'
                    THEN +[Current].Amount
                    ELSE -[Current].Amount
            END Result
 FROM       OriginalData [Current]
 LEFT JOIN  OriginalData [Previous]
        ON  [Previous].Slno < [Current].Slno
GROUP BY    [Current].Slno, [Current].Type, [Current].Amount
ORDER BY    [Current].Slno

我认为你能做出的最大改变就是改变你的心态.当您考虑以前的值"时，您选择了一个过程路径，该路径可以解决我的任何主要编程语言，但在 SQL 中迅速演变为游标方法——这在这种情况下是不合适的.

I think the biggest change you can make is to shift your mindset. When you think "previous values" you chose a procedural path which can be solved my any major programming language, but rapidly evolve to a cursor approach in SQL -- what isn't appropriate in this case.

当谈到 SQL 时，您需要以集合"的形式进行思考，以便您可以努力识别这些数据集并将它们组合起来.

When comes to SQL, you need to think in "sets", so you can drive your efforts to identify those data sets and combine them.

这篇关于如何根据条件从前一行中添加和减去值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！