mysql - 查询所有活跃贷款的每日未偿还金额

Question

假设我有一个表，其中包含向客户提供的贷款历史记录与列:

(ClientName, LoanAmount, LoanStartDate, LoanEndDate)

以及以下记录:

(Jill Clark, 100.00, 2016-01-01, 2016-01-10)
(James Smith, 200.00, 2016-01-04, 2016-01-07)
(Stewart Little , 10.00, 2016-01-05, 2016-01-06)

我想编写一个查询来列出(以某种时间序列格式)每个日历日的未偿还贷款总额。例如，查询的最终结果将是:

Date           Amount Outstanding
2016-01-01     100
2016-01-02     100
2016-01-03     100
2016-01-04     300
2016-01-05     310
2016-01-06     300
2016-01-07     100
2016-01-08     100
2016-01-09     100
2016-01-10     0

另一种选择(如果上述不可能的话)

Date           Amount Outstanding
2016-01-01     100
2016-01-04     300
2016-01-05     310
2016-01-06     300
2016-01-07     100
2016-01-10     0

这可能吗？我搜索过，没有找到类似的内容

Answer 1

在 MySQL 中，您可以使用此查询来获得替代结果(我假设您的表名为 client_loans):

SELECT Date, IFNULL(SUM(l.LoanAmount), 0) AS `Amount Outstanding`
FROM (SELECT DISTINCT LoanStartDate AS Date FROM client_loans
      UNION SELECT DISTINCT LoanEndDate FROM client_loans ORDER BY Date) AS Dates
LEFT JOIN client_loans l ON l.LoanStartDate <= Date AND l.LoanEndDate > Date 
GROUP BY Date 

Date        Amount Outstanding  
2016-01-01  100
2016-01-04  300
2016-01-05  310
2016-01-06  300
2016-01-07  100
2016-01-10  0