基于贷款历史表查询每日未偿贷款总额的SQL需求

没问题，我来帮你搞定这个需求。核心思路是先生成覆盖所有贷款起止日期范围的完整日历日期序列，再将这个日期序列与贷款表关联，计算每个日期下处于未偿状态的贷款总额。下面我会给出测试用的表结构、数据，以及针对不同主流数据库的具体查询语句。

第一步：创建测试表并插入数据 #

首先我们先还原你提供的贷款历史表，方便验证结果：

CREATE TABLE LoanHistory (
    ClientName VARCHAR(50),
    LoanAmount DECIMAL(10,2),
    LoanStartDate DATE,
    LoanEndDate DATE
);

INSERT INTO LoanHistory VALUES
('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');

第二步：不同数据库的查询实现 #

PostgreSQL 版本 #

PostgreSQL自带generate_series函数，可以快速生成日期序列：

WITH date_range AS (
    SELECT generate_series(
        (SELECT MIN(LoanStartDate) FROM LoanHistory),
        (SELECT MAX(LoanEndDate) FROM LoanHistory),
        INTERVAL '1 day'
    ) AS calendar_date
)
SELECT
    DATE(calendar_date) AS calendar_day,
    COALESCE(SUM(LoanAmount), 0.00) AS total_outstanding_loans
FROM date_range dr
LEFT JOIN LoanHistory lh
    ON dr.calendar_date BETWEEN lh.LoanStartDate AND lh.LoanEndDate
GROUP BY DATE(calendar_date)
ORDER BY calendar_day;

MySQL 8.0+ 版本 #

MySQL 8.0及以上支持递归CTE来生成日期序列：

WITH RECURSIVE date_range AS (
    SELECT MIN(LoanStartDate) AS calendar_date FROM LoanHistory
    UNION ALL
    SELECT DATE_ADD(calendar_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE calendar_date < (SELECT MAX(LoanEndDate) FROM LoanHistory)
)
SELECT
    calendar_date AS calendar_day,
    COALESCE(SUM(LoanAmount), 0.00) AS total_outstanding_loans
FROM date_range dr
LEFT JOIN LoanHistory lh
    ON dr.calendar_date BETWEEN lh.LoanStartDate AND lh.LoanEndDate
GROUP BY calendar_date
ORDER BY calendar_day;

SQL Server 版本 #

SQL Server同样使用递归CTE，注意需要处理递归次数限制：

WITH date_range AS (
    SELECT MIN(LoanStartDate) AS calendar_date FROM LoanHistory
    UNION ALL
    SELECT DATEADD(DAY, 1, calendar_date)
    FROM date_range
    WHERE calendar_date < (SELECT MAX(LoanEndDate) FROM LoanHistory)
)
SELECT
    calendar_date AS calendar_day,
    ISNULL(SUM(LoanAmount), 0.00) AS total_outstanding_loans
FROM date_range dr
LEFT JOIN LoanHistory lh
    ON dr.calendar_date BETWEEN lh.LoanStartDate AND lh.LoanEndDate
GROUP BY calendar_date
ORDER BY calendar_day
OPTION (MAXRECURSION 0); -- 当日期范围超过100天时需要添加此选项

结果说明 #

执行上述查询后，你会得到类似这样的结果：

每个日期的未偿总额会自动统计所有当天处于贷款周期内的金额，没有未偿贷款的日期会显示0.00（如果你的日期范围超出现有贷款的起止日期）。

内容的提问来源于stack exchange，提问作者Jay Fern