生成以下输出的 sql 查询
我有一个表account(ID,TransDate,amount,isDebit,balance)。显然，为了清楚起见，这是我的表格的精简版本。 TransDate 是输入的日期，isDebit 是一个布尔值，指示此交易是借记还是贷记。
当创建新条目时，它的 transDate 可能比最后一个条目旧，因此我必须将其放置在正确的位置，并更改该位置下方的所有行的余额。在那里，我按 transDate ASC 、 ID ASC 对行进行排序，这为我提供了正确的条目顺序。
余额将通过将当前金额添加到之前的最后余额 ID DEBIT 来生成，如果当前条目是 CREDIT，则扣除。
它具有以下最后 5 个条目按 transDate ASC 、 ID ASC 排序：以
account(ID,TransDate,amount,isDebit,balance) 为例
41 | 2011-04-10 | 1000 | 1 | 1000 // as this is the first entry but not always
37 | 2011-05-14 | 7500 | 1 | 8500
39 | 2011-05-14 | 6500 | 0 | 2000
46 | 2011-05-15 | 1000 | 1 | 3000
任何人都可以生成查询：
要创建一个新 查询包含以下详细信息的条目：
查询应该发现新的条目位置位于 ID 为 41 的行之后、ID 为 37 的行之前。并且还从 Id 41 中获取行（因为这行的余额将用于计算新行的余额）和下面，我在其上设置新的更正余额。
或者提出一种新的方法来实施这种“分类账核算”。
I have a table account(ID,TransDate,amount,isDebit,balance). Obviously this is a trimmed down version of my table for sake of clarity. TransDate is the date for which the entry will be made, isDebit is a boolean which tells if this transaction is a DEBIT or CREDIT.
A when a new entry is made it could have a transDate older then the last entry made, so i have to position it in right place and change the balances of all the rows which will be below that position. There I sort the rows by transDate ASC , ID ASC , which gives me the correct order of entries.
Balance will be generated by adding current amount to previous last balance id DEBIT and deducted if current entry is CREDIT.
It has the following last 5 entries sorted by transDate ASC , ID ASC:
account(ID,TransDate,amount,isDebit,balance) as an example
41 | 2011-04-10 | 1000 | 1 | 1000 // as this is the first entry but not always
37 | 2011-05-14 | 7500 | 1 | 8500
39 | 2011-05-14 | 6500 | 0 | 2000
46 | 2011-05-15 | 1000 | 1 | 3000
Can anyone generate a query(s):
To make a new entry with following details : <ID auto> | 2011-05-9 | 1000 | 1 | <balance>
the query should find that the new entry position is after row with ID 41 and before row ID 37. and also fetches me rows from Id 41 (as this row's balance will be used to calculate the balance for the new row) and below on which i set the new corrected balances.
Or suggest a new method to implement this kind of "ledger accounting".
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
不幸的是，MySQL 不支持窗口函数，但您也许可以从之前涉及存储过程的答案中得出解决方案，因为 SQL Server 的窗口函数支持对于此类查询也太有限：计算 SQL Server 中的运行总计
Unfortunately, MySQL has no support for window functions, but you might be able to derive a solution from this previous answer involving stored procedures, as SQL Server's window function support is too limited for this kind of query as well: Calculate a Running Total in SQL Server