MySQL 根据发票获取每日流入/流出？

Question

首先是我的 table :

mysql> desc invoice;
+-------------+-----------------------+------+-----+---------+----------------+
| Field       | Type                  | Null | Key | Default | Extra          |
+-------------+-----------------------+------+-----+---------+----------------+
| id          | int(11)               | NO   | PRI | NULL    | auto_increment |
| date        | timestamp             | YES  |     | NULL    |                |
| sent        | timestamp             | YES  |     | NULL    |                |
| due_date    | timestamp             | YES  |     | NULL    |                |
| amount      | float                 | YES  |     | NULL    |                |
| amount_due  | float                 | YES  |     | NULL    |                |
| status      | enum('unpaid','paid') | YES  |     | NULL    |                |
| customer_id | int(11)               | NO   | MUL | NULL    |                |
+-------------+-----------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

任何人都可以帮我想出一个将返回的查询，对于每一行:

日期(所以按 due_date 分组)当天的期初余额(这将是前一天的期末余额)流入(当天的正交易总额)流出量(当天的负交易总数)期末余额(所有流入和流出交易应用后的当日期末余额)

基本上每天我都想知道存了多少钱，取了多少钱，当天的余额是什么时候开始的，什么时候结束的。

我还应该指定当天，我想按“到期日期”对行进行分组，并使用“金额”列作为所有交易的基础(忽略到期金额)。

有什么想法吗？

我可以开始制作另一个只包含此信息的表，但我认为也许有人可以想出一个漂亮的查询来根据实际数据执行此操作。

Answer 1

这是我的看法。基本上你通过

加入发票表

todaydata.due_date=DATE_ADD(yesterdaydata.due_date,DAY,-1)

所以你可以访问昨天的 amount_due 字段(我做了一个左连接所以如果你在你的表中请求第一天，你仍然会得到一个记录)

剩下的就很简单了。我不确定今天的期末余额是昨天+流入+流出的总和还是只是今天data.amount_due的总和，所以我把两者都包括在内。

SELECT 
todaydata.due_date,
SUM(case when yesterdaydata.amount_due is null then 0 else yesterdaydata.amount_due end )) AS StartingBalance,
SUM(CASE when todaydata.amount > 0 THEN todaydata.amount ELSE 0 END)  AS INFLOW,
SUM(CASE when todaydata.amount < 0 THEN todaydata.amount ELSE 0 END)  AS OUTFLOW,
SUM(yesterday.amount_due) AS StartingBalance +
            SUM(CASE where todaydata.amount > 0 THEN todaydata.amount ELSE 0 END)  + 
            SUM(CASE where todaydata.amount < 0 THEN todaydata.amount ELSE 0 END)  AS CalculatedEndingBalance,
SUM(todaydata.amount_due) AS OtherEndingBalance
FROM invoice [todaydata]
LEFT JOIN invoice [yesterdaydata] ON todaydata.due_date=DATE_ADD(yesterdaydata.due_date,DAY,-1)
GROUP BY todaydata.due_date

我没有能力针对 mysql 数据库进行测试，所以让我知道任何语法错误，我会改正。