如何在Delphi+Firebird环境下计算伙伴债务小计与最终值

环境与数据结构 #

• 开发环境：Delphi 10.4 + Firebird 3
• 核心数据表：
  • incomes：收入记录
  • sales：销售记录
  • Payments：伙伴向我方支付的款项（收入款项）
  • MyPayments：我方向伙伴支付的款项（销售款项）
  • Partners：伙伴信息表，其中存储初始债务的字段：
    • idt：伙伴欠我方的金额（示例值：10000）
    • myidt：我方欠伙伴的金额（示例值：-10000）
    • idt_date：初始债务的基准日期（示例值：2020/01/01）

债务计算规则（指定日期如2023/05/20） #

1. 以idt与myidt的合计值作为初始债务
2. 减去初始日期到指定日期区间内的收入总和
3. 加上区间内的销售总和
4. 减去区间内伙伴的付款总和
5. 加上区间内我方的付款总和

现有SQL问题 #

当前查询仅合并了各类交易记录，但未实现核心需求：

• 未包含初始债务作为计算起点
• 仅计算单条记录的债务变动，无累计阶段性小计
• 字段笔误：将Partners表的idt_date误写为ifd_date/ifd_dt，导致日期筛选错误
• 缺少最终债务汇总行

修正后的SQL查询 #

WITH AllTransactions AS (
    -- 1. 初始债务记录（计算起点）
    SELECT
        p.partner_id,
        'INIT' AS Docid,
        p.idt_date AS DocDate,
        0 AS Incomes,
        0 AS Sales,
        0 AS Payments,
        0 AS MyPayments,
        COALESCE(p.idt, 0) + COALESCE(p.myidt, 0) AS DebtChange
    FROM Partners p
    WHERE p.partner_id = :partner_id

    UNION ALL

    -- 2. 收入记录：收入减少伙伴欠我方的债务
    SELECT
        i.partner_id,
        i.Docid,
        i.DocDate,
        i.summa AS Incomes,
        0 AS Sales,
        0 AS Payments,
        0 AS MyPayments,
        -i.summa AS DebtChange
    FROM Incomes i
    JOIN Partners p ON i.partner_id = p.partner_id
    WHERE p.partner_id = :partner_id
      AND i.DocDate >= p.idt_date
      AND i.DocDate <= :d

    UNION ALL

    -- 3. 销售记录：销售增加我方欠伙伴的债务
    SELECT
        s.partner_id,
        s.Docid,
        s.DocDate,
        0 AS Incomes,
        s.summa AS Sales,
        0 AS Payments,
        0 AS MyPayments,
        s.summa AS DebtChange
    FROM Sales s
    JOIN Partners p ON s.partner_id = p.partner_id
    WHERE p.partner_id = :partner_id
      AND s.DocDate >= p.idt_date
      AND s.DocDate <= :d

    UNION ALL

    -- 4. 伙伴付款记录：伙伴还款减少债务
    SELECT
        pay.partner_id,
        pay.Docid,
        pay.DocDate,
        0 AS Incomes,
        0 AS Sales,
        pay.summa AS Payments,
        0 AS MyPayments,
        -pay.summa AS DebtChange
    FROM Payments pay
    JOIN Partners p ON pay.partner_id = p.partner_id
    WHERE p.partner_id = :partner_id
      AND pay.DocDate >= p.idt_date
      AND pay.DocDate <= :d

    UNION ALL

    -- 5. 我方付款记录：我方还款增加债务
    SELECT
        mypay.partner_id,
        mypay.Docid,
        mypay.DocDate,
        0 AS Incomes,
        0 AS Sales,
        0 AS Payments,
        mypay.summa AS MyPayments,
        mypay.summa AS DebtChange
    FROM MyPayments mypay
    JOIN Partners p ON mypay.partner_id = p.partner_id
    WHERE p.partner_id = :partner_id
      AND mypay.DocDate >= p.idt_date
      AND mypay.DocDate <= :d
),
TransactionWithCumulative AS (
    -- 计算累计债务（阶段性小计）
    SELECT
        partner_id,
        Docid,
        DocDate,
        Incomes,
        Sales,
        Payments,
        MyPayments,
        DebtChange,
        SUM(DebtChange) OVER (ORDER BY DocDate, Docid) AS CumulativeDebt
    FROM AllTransactions
)
-- 输出阶段性记录 + 最终汇总行
SELECT
    partner_id,
    Docid,
    DocDate,
    Incomes,
    Sales,
    Payments,
    MyPayments,
    CumulativeDebt AS 当前债务
FROM TransactionWithCumulative

UNION ALL

-- 最终汇总：显示所有交易总和及最终债务
SELECT
    :partner_id AS partner_id,
    'FINAL' AS Docid,
    :d AS DocDate,
    SUM(Incomes) AS Incomes,
    SUM(Sales) AS Sales,
    SUM(Payments) AS Payments,
    SUM(MyPayments) AS MyPayments,
    MAX(CumulativeDebt) AS 当前债务
FROM TransactionWithCumulative
ORDER BY DocDate, Docid;

修正说明 #

1. 添加初始债务起点：将伙伴的初始债务作为第一条记录加入，确保计算从正确的基准值开始
2. 修正字段笔误：统一将错误的ifd_date/ifd_dt改为Partners表的正确字段idt_date
3. 阶段性累计计算：使用Firebird支持的窗口函数SUM() OVER ()，按日期和单据号排序，计算每一步的累计债务
4. 新增最终汇总：通过UNION ALL添加汇总行，直观展示所有交易的合计值及最终债务结果
5. 明确债务变动逻辑：为每类交易定义明确的DebtChange值，完全匹配需求中的计算规则

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