在SQLite表中插入每个日历日期的记录

Question

我在SQLite3数据库中有一个包含帐户余额的表，但它目前只包含几个特定日期的余额：

金额

余额日期
2021-12-15	400
2021-12-18	500
2021-12-2	200

您可以使用递归cte来生成丢失的日期：

WITH cte AS (
SELECT date(b1.BalanceDate, '+1 day') BalanceDate, b1.Amount 
FROM BALANCES b1
WHERE NOT EXISTS (SELECT 1 FROM BALANCES b2 WHERE b2.BalanceDate = date(b1.BalanceDate, '+1 day'))
AND date(b1.BalanceDate, '+1 day') < (SELECT MAX(BalanceDate) FROM BALANCES)
UNION ALL
SELECT date(c.BalanceDate, '+1 day'), c.Amount 
FROM cte c
WHERE NOT EXISTS (SELECT 1 FROM BALANCES b WHERE b.BalanceDate = date(c.BalanceDate, '+1 day'))
AND date(c.BalanceDate, '+1 day') < (SELECT MAX(BalanceDate) FROM BALANCES)
)
INSERT INTO BALANCES(BalanceDate, Amount)
SELECT BalanceDate, Amount FROM cte;

请参阅演示