时间戳

首页 > 解决方案 > 带逻辑的 SQL Server 查询，外部应用

sql - 带逻辑的 SQL Server 查询，外部应用

问题描述

我需要有关以下查询的帮助。我正在尝试从金额列中减去每个 acc_No 的扣除金额。运行金额列应随着该帐号的每个金额而减少。在完成所有减法之后，如果还有任何金额，则应该在 reportAmount 列上。我需要帮助的报告列，并且“运行金额”列中的某些条目不匹配。

DECLARE @table AS TABLE
                  (
                      acc_no INT, 
                      [group] CHAR(1), 
                      DeductAmount FLOAT, 
                      amount FLOAT
                  )

INSERT INTO @table 
VALUES (101, 'A', -100, 0), (101, 'B', -100, 20),
       (101, 'C', -100, 70), (101, 'D', -100, 20),
       (102, 'A', -20, 30), (102, 'B', -20, 0),
       (102, 'C', -20, 5), (102, 'D', -20, 10),
       (103, 'A', -80, 40), (103, 'B', -80,15),
       (103, 'C', -80, 10), (103, 'D', -80, 5),
       (104, 'A', -30, 0), (104, 'B', -30,85),
       (104, 'C', -30, 5), (104, 'D', -30, 10),
       (105, 'A', -15, 7), (105, 'B', -15,25),
       (105, 'C', -15, 5), (105, 'D', -15, 10)

-- SELECT * FROM @table

SELECT 
    t.acc_no, 
    t.[group],
    t.DeductAmount,amount,--t1.LeftFromDeduct, 
    CASE 
       WHEN (t.DeductAmount + t1.LeftFromDeduct) >= 0 
          THEN 0 
          ELSE (t.DeductAmount + t1.LeftFromDeduct) 
    END RunningAmount
FROM
    @table t
OUTER APPLY
    (SELECT  
         SUM([amount]) AS LeftFromDeduct 
     FROM 
         @table t1 
     WHERE 
         t1.acc_no = t.acc_no AND t1.[group] <= t.[group]) t1

当我执行它时，我得到以下结果：

acc_no  group   DeductAmount    amount  RunningAmount
-----------------------------------------------------
101 A   -100    0   -100
101 B   -100    20  -80
101 C   -100    70  -10
101 D   -100    20  0
102 A   -20 30  0
102 B   -20 0   0
102 C   -20 5   0
102 D   -20 10  0
103 A   -80 40  -40
103 B   -80 15  -25
103 C   -80 10  -15
103 D   -80 5   -10
104 A   -30 0   -30
104 B   -30 85  0
104 C   -30 5   0
104 D   -30 10  0
105 A   -15 7   -8
105 B   -15 25  0
105 C   -15 5   0
105 D   -15 10  0

我的预期结果应该是这样的，我需要帮助来纠正逻辑以找到 runningAmount 和 ReportAmount 列。

acc_no  group   DeductAmount    amount  RunningAmount   ReportAmount
101 A   -100    0   -100    0
101 B   -100    20  -80 0
101 C   -100    70  -10 0
101 D   -100    20  10  10
102 A   -20 30  0   10
102 B   -20 0   0   0
102 C   -20 5   0   5
102 D   -20 10  0   10
103 A   -80 40  -40 0
103 B   -80 15  -25 0
103 C   -80 10  -15 0
103 D   -80 5   -10 0
104 A   -30 0   -30 0
104 B   -30 85  0   55
104 C   -30 5   0   5
104 D   -30 10  0   10
105 A   -15 7   -8  0
105 B   -15 25  -7  18
105 C   -15 5   0   5
105 D   -15 10  0   10

标签： sqlsql-serverleft-join

解决方案

根据您的描述，您似乎想要：

select t.*,
       (case when runningamount_tmp > 0 then 0 else runningamount_tmp end) as running_amount,
       (case when runningamount_tmp > 0 then runningamount_tmp else 0 end) as reportamount
from (select t.*,
             (deductamount + sum(amount) over (partition by acc_no order by group)) as runningamount_tmp     
      from @table t
     ) t;

推荐阅读