英文:
Converting Ending Balance to Beginning Balance in MySQL for Accounting Data
问题
我有一组需要使用MySQL查询的会计数据，但我在弄清楚如何将一个月的期末余额转换为下个月的期初余额方面遇到了问题。
这里有一个例子：在2020年4月，账户101的期末余额为$200。我希望这$200成为2020年5月同一账户（101）的期初余额。
我已经在网上搜索了解决方案，但它们都假定你在处理当前日期，这对我来说不起作用。有人可以帮助我吗？我真的会非常感激！
表格目前如下所示：
| 日期 | 账户 | 名称 | 余额 |
|---|---|---|---|
| 2020年4月30日 | 101 | 机器 | $200 |
| 2020年5月30日 | 101 | 机器 | $300 |
| 2020年6月30日 | 101 | 机器 | $400 |
| 2020年7月30日 | 101 | 机器 | $500 |
预期输出：
| 日期 | 账户 | 名称 | 期初余额 | 期末余额 |
|---|---|---|---|---|
| 2020年4月30日 | 101 | 机器 | null | $200 |
| 2020年5月30日 | 101 | 机器 | $200 | $300 |
| 2020年6月30日 | 101 | 机器 | $300 | $400 |
| 2020年7月30日 | 101 | 机器 | $400 | $500 |
请注意，表格被极度简化了。有数百个账户，有多年的交易，每天都有多次交易。
到目前为止，我所做的只是在仪表板方面，所以相当繁琐。如果可以通过SQL来实现，那将会简单得多。
英文:
I've got a set of accounting data that I need to query using MySQL, but I'm having trouble figuring out how to convert the ending balance of a month into the beginning balance for the next month.
Here's an example: In April 2020, the ending balance for account 101 is $200. I want this $200 to be the starting balance for May 2020 for the same account (101).
I've searched online for solutions, but they all assume you're working with the current date, which doesn't work for me. Can anyone help me out with this? I'd really appreciate it!
The table currently looks like this:
| Date | Account | Name | Balance |
|---|---|---|---|
| 30 Appril 2020 | 101 | Machine | $200 |
| 30 May 2020 | 101 | Machine | $300 |
| 30 June 2020 | 101 | Machine | $400 |
| 30 July 2020 | 101 | Machine | $500 |
Expected Output:
| Date | Account | Name | Beginning Balance | Ending Balance |
|---|---|---|---|---|
| 30 Appril 2020 | 101 | Machine | null | $200 |
| 30 May 2020 | 101 | Machine | $200 | $300 |
| 30 June 2020 | 101 | Machine | $300 | $400 |
| 30 July 2020 | 101 | Machine | $400 | $500 |
Please note that the table is grossly oversimplified. There are hundreds of accounts, and there are years of transaction with multiple transactions per day.
As of now what I have done is just on the dashboard side, so quite tedious. If it is possible to do it through Sql, that would make it so much simpler.
答案1
得分: 0
使用 INSERT INTO ... SELECT，其中 SELECT 查询仅复制四月底余额到五月。
INSERT INTO yourTable (date, account, name, beginning_balance, ending_balance)
SELECT '30 May 2020', account, name, ending_balance, NULL
FROM yourTable
WHERE date = '30 April 2020'
英文:
Use INSERT INTO ... SELECT, where the SELECT query simply copies the April ending balance to May.
INSERT INTO yourTable (date, account, name, beginning_balance, ending_balance)
SELECT '30 May 2020', account, name, ending_balance, NULL
FROM yourTable
WHERE date = '30 April 2020'
答案2
得分: 0
如@GMB在评论中提到的，您可以使用窗口函数lag()来检索当前行之前的行：
ALTER TABLE mytable ADD COLUMN beginning_Balance int;
UPDATE mytable t
INNER JOIN (
SELECT id, LAG(ending_Balance) OVER(PARTITION BY account ORDER BY date) AS beginning_Balance
FROM mytable
) AS s ON s.id = t.id
SET t.beginning_Balance = s.beginning_Balance;
英文:
As mentioned by @GMB in comments you can use the window function lag() to retrieve the row that comes before the current row :
ALTER TABLE mytable ADD COLUMN beginning_Balance int;
UPDATE mytable t
INNER JOIN (
select id, LAG(ending_Balance) OVER(PARTITION BY account ORDER BY date) as beginning_Balance
FROM mytable
) as s ON s.id = t.id
SET t.beginning_Balance = s.beginning_Balance
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论