需要在 Sqlite 数据库中运行总计
我有一个 Sqlite 数据库，其中有一个包含金融交易的表。相关列包括日期、采购订单号以及借方和贷方金额。我需要显示这些交易，并根据任意起始余额（始终是第一条记录）计算运行总计。
我根据此查询创建了一个视图：
SELECT t1.[ID], t1.[Date], t1.[PO], t1.[Debit], t1.[Credit],
( SELECT SUM( t2.[Credit] ) - SUM ( t2.[Debit] )
FROM [Transaction] t2
WHERE t2.[ID] <= t1.[ID] ) AS [Balance]
FROM [Transaction] t1
并且它有效。问题是，有时我需要按日期和/或 PO# 订购，当我这样做时，虽然余额列值是正确的，但它是乱序的。另外，我需要按日期进行过滤，所以我不相信我可以在事务表中使用计算列。
我已经研究过使用 ROWID 值，但没有任何变化。据我所知，Sqlite不支持ROW_NUMBER() OVER()。我花了几天时间试图解决这个问题，但没有成功。
这是一个 .NET Compact Framework 应用程序，Sqlite ADO.NET 版本 1.0.66（不确定 Sqlite 引擎是什么版本）。
I have a Sqlite database with a table containing financial transactions. Pertinent columns are date, PO#, and debit and credit amounts. I need to display these transactions and have a running total calculated based on an arbitrary starting balance (which is always the first record).
I created a view based on this query:
SELECT t1.[ID], t1.[Date], t1.[PO], t1.[Debit], t1.[Credit],
( SELECT SUM( t2.[Credit] ) - SUM ( t2.[Debit] )
FROM [Transaction] t2
WHERE t2.[ID] <= t1.[ID] ) AS [Balance]
FROM [Transaction] t1
and it works. Problem is, sometimes I need to order by date and/or PO# and when I do, while the Balance column value is correct, it's out of order. Also, I'll need to filter by date, so I don't believe I can use a computed column in the Transaction table.
I've looked into using the ROWID value, but no change. As far as I can find, Sqlite doesn't support ROW_NUMBER() OVER(). I've spent a couple days trying to wrap my head around this with no luck.
This a .NET Compact Framework app, Sqlite ADO.NET version 1.0.66 (not sure what version of the Sqlite engine that is).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
解决方案确实是在表中添加一个余额字段，并在添加/编辑/删除记录时通过触发器更新它。事实证明，我真正的问题并不是如何进行总计，而是做我想做的事情的最佳实践，这是 MPelletier 建议的银行家数据库。我在 此线程 中找到了所需的触发器示例经过一些调整，我就可以做我需要做的事情了。因此，我不再需要该视图，因为它的唯一目的是用于运行总计。
The solution was indeed to add a Balance field to the table, and update it via triggers when records are added/edited/deleted. Turns out my real question wasn't so much how to do a running total, but best practice for doing what I was trying to do, which was a banker's database as suggested by MPelletier. I found examples of the triggers I needed in this thread which, with a little tweaking, I got to do what I needed. Consequently, I no longer require the view since its only purpose was for the running total.