我有一个名为transaction_table
的表:
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
因此,我只做了SELECT
语句,该语句将根据date
和_id
获得余额,但余额是错误的:
SELECT t1._id,
t1.date ,
t1.description ,
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
我可以通过以下代码实现:
SELECT _id,date, description, debit, credit,
SUM(debit - credit) OVER (PARTITION BY curr_id, cus_id ORDER BY date, _id) blnc,
curr_id, cus_id
FROM transaction_table
order by date desc, _id desc;
但我不需要它,因为它不工作在旧的android.我只需要使用INNER JOIN
.
2条答案
按热度按时间qfe3c7zg1#
ON
子句中有关日期和id的条件需要更正:请参阅demo。
piok6c0g2#
可以用相关子查询替换
SUM()
分析函数调用: