mysql - 处理交易表中的到期信用？

Question

考虑表 Credits

 CREATE TABLE `Credits` (
  `UserID` int(11) unsigned NOT NULL DEFAULT '0',
  `Amount` int(11) NOT NULL DEFAULT '0',
  `Created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Expire` datetime NOT NULL DEFAULT '9999-12-31 23:59:59'
) 

有数据:

UserID    Amount    Created       Expire
1         10        2016-01-14    2016-05-30
1         -2        2016-02-04    9999-12-31
1         3         2016-06-01    2016-09-30

没有Expiry处理，获取用户当前数量，简单select即可处理

 SELECT SUM(Amount) FROM Credits WHERE UserID = 1;

现在，我需要写一个SELECT查询，输入参数为date，并且能够获取当时可用的信用额度，如下所示。

在..

• 2016-01-15 => 10
• 2016-02-06 => 8
• 2016-05-31 => 0
• 2016-06-03 => 3

仅使用上述架构是否可行？或者我需要添加额外的字段？

SQLFiddle:http://sqlfiddle.com/#!9/3a52b/3

Answer 1

当您进行负金额的交易时，您应该找到相应的正金额的交易并设置相同的到期日期。这样，您就可以存储所花费的积分的到期日期。您的示例表将如下所示:

UserID    Amount    Created       Expire
1         10        2016-01-14    2016-05-30
1         -2        2016-02-04    2016-05-30
1         3         2016-06-01    2016-09-30

这使得查询任何特定日期的余额如下所示:

SELECT SUM(Amount) FROM Credits WHERE UserID = 1 and @date between Created and Expire;

请注意，您可能需要拆分一笔金额为负的交易，以涵盖到期日不同的信用额度。例如，您有下表:

UserID    Amount    Created       Expire
1         10        2016-01-14    2016-05-30
1         10        2016-02-04    2016-06-30

而你想用Amount=-15进行交易，那么你需要做两条记录:

UserID    Amount    Created       Expire
1         -10       2016-04-26    2016-05-30
1         -5        2016-04-26    2016-06-30

要找出尚未花费或过期的信用及其到期日期，您可以使用以下查询:

select sum(Amount) as Amount, Expire
from Credits
where UserID = 1 and curdate() <= Expire
group by Expire
having sum(Amount) > 0
order by Expire