如何将EventLedger交易数据按account_nr汇总至ListOfAccounts对应列

背景与表结构 #

目前基于Python+SQLite开发会计程序，涉及三张核心表：

• ListOfAccounts：包含account_nr、account_name、account_type、debit、credit、balance共6列
• GeneralLedger：包含entry_id（主键）、entry_date、description共3列
• EventLedger：包含primary_id（主键）、entry_id（外键关联GeneralLedger）、account_nr_E、debit、credit共5列

交易录入逻辑：每笔交易先在GeneralLedger生成主记录，再在EventLedger生成多条关联明细，将金额分配至对应账户。

当前需求：按account_nr_E分组汇总EventLedger的debit和credit总额，更新到ListOfAccounts的对应列，但多次尝试要么报错，要么ListOfAccounts的debit/credit列仍为0。

尝试过的SQL代码 #

SELECT L.account_name, SUM(E.debit) AS Balance
FROM EventLedger E 
JOIN ListOfAccounts L ON L.account_nr = E.account_nr_E
GROUP BY L.account_name

表创建语句 #

ListOfAccounts #

CREATE TABLE IF NOT EXISTS
ListOfAccounts(
account_nr INTEGER,
account_name TEXT,
account_type TEXT,
debit REAL NOT_NULL, 
credit REAL NOT_NULL,
balance REAL NOT_NULL);

EventLedger #

CREATE TABLE IF NOT EXISTS
EventLedger(
primary_id INTEGER NOT_NULL PRIMARY KEY,
entry_id INTEGER,
account_nr_E INTEGER,
debit REAL,
credit REAL);

示例数据与预期效果 #

EventLedger原始数据 #

预期更新后的ListOfAccounts数据 #

解决方案 #

你之前只执行了查询操作，没有触发UPDATE语句，另外需要处理NULL值（EventLedger中debit/credit可能为空），且分组依据应使用唯一标识account_nr而非account_name。

基础更新语句 #

UPDATE ListOfAccounts
SET debit = (
    SELECT COALESCE(SUM(debit), 0)
    FROM EventLedger
    WHERE EventLedger.account_nr_E = ListOfAccounts.account_nr
),
credit = (
    SELECT COALESCE(SUM(credit), 0)
    FROM EventLedger
    WHERE EventLedger.account_nr_E = ListOfAccounts.account_nr
);

同步更新balance列（可选） #

如果需要同时计算账户余额：

UPDATE ListOfAccounts
SET debit = (
    SELECT COALESCE(SUM(debit), 0)
    FROM EventLedger
    WHERE EventLedger.account_nr_E = ListOfAccounts.account_nr
),
credit = (
    SELECT COALESCE(SUM(credit), 0)
    FROM EventLedger
    WHERE EventLedger.account_nr_E = ListOfAccounts.account_nr
),
balance = (
    SELECT COALESCE(SUM(debit), 0) - COALESCE(SUM(credit), 0)
    FROM EventLedger
    WHERE EventLedger.account_nr_E = ListOfAccounts.account_nr
);

性能优化版本（SQLite 3.33.0+支持） #

使用WITH子查询减少重复扫描EventLedger：

WITH AccountSummary AS (
    SELECT
        account_nr_E,
        COALESCE(SUM(debit), 0) AS total_debit,
        COALESCE(SUM(credit), 0) AS total_credit
    FROM EventLedger
    GROUP BY account_nr_E
)
UPDATE ListOfAccounts
SET
    debit = COALESCE((SELECT total_debit FROM AccountSummary WHERE account_nr_E = ListOfAccounts.account_nr), 0),
    credit = COALESCE((SELECT total_credit FROM AccountSummary WHERE account_nr_E = ListOfAccounts.account_nr), 0),
    balance = COALESCE((SELECT total_debit - total_credit FROM AccountSummary WHERE account_nr_E = ListOfAccounts.account_nr), 0);

关键说明 #

• COALESCE(SUM(debit), 0)：处理账户无交易时SUM返回NULL的情况，确保ListOfAccounts的列不会被设为NULL
• 子查询通过account_nr关联两张表，保证每个账户的借贷总额精准匹配

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