如何使用INNER JOIN按日期和ID计算各客户及币种的账户余额

表结构 #

CREATE TABLE transaction_table
  (
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    date TEXT,
    debit REAL,
    credit REAL,
    curr_id INTEGER,
    cus_id INTEGER,
    FOREIGN KEY (curr_id) REFERENCES currencies(_id) ON DELETE CASCADE,
    FOREIGN KEY (cus_id) REFERENCES customers(_id) ON DELETE CASCADE
  )

表中数据 #

_id  date                       debit    credit   curr_id    cus_id
-------------------------------------------------------------------
1   2022-12-08T00:00:00.000     10.0       0.0         1         1
2   2022-12-07T00:00:00.000      0.0      20.0         1         1
3   2022-12-06T00:00:00.000      0.0      30.0         1         1
4   2022-12-07T00:00:00.000     40.0       0.0         1         1
5   2022-12-08T00:00:00.000    100.0       0.0         1         1

错误的SQL语句 #

尝试通过INNER JOIN计算每个cus_id和curr_id按date和_id排序的余额，但结果错误：

SELECT  t1._id,
    t1.date ,
    t1.debit ,
    t1.credit,
    SUM(t2.debit - t2.credit) as blnc,
    t1.curr_id,
    t1.cus_id
FROM transaction_table t1 INNER JOIN transaction_table t2
ON t2.curr_id = t1.curr_id AND t2.cus_id = t1.cus_id AND t2._id <= t1._id AND t2.date <= t1.date
GROUP BY t1._id
ORDER BY t1.date DESC, t1._id DESC;

错误结果 #

_id    date                        debit   credit   balance   curr_id   cus_id
  -----------------------------------------------------------------------------
   5    2022-12-08T00:00:00.000     100.0      0.0     100.0         1        1
   1    2022-12-08T00:00:00.000      10.0      0.0      10.0         1        1
   4    2022-12-07T00:00:00.000      40.0      0.0     -10.0         1        1
   2    2022-12-07T00:00:00.000       0.0     20.0     -20.0         1        1
   3    2022-12-06T00:00:00.000       0.0     30.0     -30.0         1        1

预期正确结果 #

_id    date                        debit   credit   balance   curr_id   cus_id
  -----------------------------------------------------------------------------
   5    2022-12-08T00:00:00.000     100.0      0.0     100.0         1        1
   1    2022-12-08T00:00:00.000      10.0      0.0       0.0         1        1
   4    2022-12-07T00:00:00.000      40.0      0.0     -10.0         1        1
   2    2022-12-07T00:00:00.000       0.0     20.0     -50.0         1        1
   3    2022-12-06T00:00:00.000       0.0     30.0     -30.0         1        1

需求说明 #

已知用窗口函数可以实现正确结果，但旧版Android不支持窗口函数，需要仅使用INNER JOIN的正确SQL语句。

解决方案 #

问题出在连接条件的逻辑上，原语句同时用t2._id <= t1._id和t2.date <= t1.date会导致重复计算、累计逻辑混乱。正确的连接条件应该遵循先按date排序，date相同则按_id排序的规则，即判断t2.date < t1.date，或者t2.date = t1.date AND t2._id <= t1._id。

修正后的SQL语句：

SELECT
    t1._id,
    t1.date,
    t1.debit,
    t1.credit,
    SUM(t2.debit - t2.credit) AS balance,
    t1.curr_id,
    t1.cus_id
FROM transaction_table t1
INNER JOIN transaction_table t2
    ON t2.curr_id = t1.curr_id
    AND t2.cus_id = t1.cus_id
    AND (t2.date < t1.date OR (t2.date = t1.date AND t2._id <= t1._id))
GROUP BY t1._id, t1.date, t1.debit, t1.credit, t1.curr_id, t1.cus_id
ORDER BY t1.date DESC, t1._id DESC;

逻辑说明 #

• 连接条件确保每条t1记录仅汇总日期早于自身的所有交易，或者日期相同但_id小于等于自身的交易，保证余额按时间顺序正确累计。
• 分组时包含所有非聚合字段，兼容旧版Android使用的SQLite分组规则。

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