如何在SQL中计算累计扣除INT后的PRIN余额

现有表信息 #

表名：tbl9，字段说明：

• DT：日期类型
• PRIN：VARCHAR2类型（存储本金数值）
• INT：VARCHAR2类型（存储利息数值）

表中数据：

计算需求 #

新增BAL列，计算规则：

• 第一行：本金(PRIN) - 当前行利息(INT)
• 第二行及后续行：本金(PRIN) - 当前行及所有前置行的利息总和

期望结果：

SQL解决方案 #

使用窗口函数SUM() OVER()计算累计利息总和，直接得到符合要求的余额：

SELECT 
    DT,
    PRIN,
    INT,
    TO_NUMBER(PRIN) - SUM(TO_NUMBER(INT)) OVER(ORDER BY DT) AS BAL
FROM tbl9;

关键说明 #

1. SUM(TO_NUMBER(INT)) OVER(ORDER BY DT)：按日期升序，自动计算从第一行到当前行的利息累计值，完美匹配需求中"当前及所有前置行INT总和"的要求
2. 由于原表中PRIN和INT为VARCHAR2类型，必须通过TO_NUMBER()转换为数值类型才能执行算术运算

建表及插入数据脚本 #

CREATE TABLE tbl9 ( 
    DT DATE, 
    PRIN VARCHAR2(20), 
    INT VARCHAR2(20)
);

INSERT INTO tbl9 VALUES(TO_DATE('01-01-2023','dd-mm-yyyy'),100000, 1100);
INSERT INTO tbl9 VALUES(TO_DATE('01-02-2023','dd-mm-yyyy'),100000, 1200);
INSERT INTO tbl9 VALUES(TO_DATE('01-03-2023','dd-mm-yyyy'),100000, 1500);
INSERT INTO tbl9 VALUES(TO_DATE('01-04-2023','dd-mm-yyyy'),100000, 2300);

COMMIT;

内容的提问来源于stack exchange，提问作者Erik