MySQL如何根据不同日期范围计算4组不同SUM汇总值

该需求完全合理，属于业务中非常常见的分时间段聚合统计场景，无需拆分多次查询，单条SQL即可高效实现。

不需要在子查询的WHERE子句中加日期过滤条件，直接把日期范围判断嵌入到SUM聚合的CASE WHEN条件中，一次遍历交易表就能计算出所有四个时段的汇总指标，避免多次关联查询，性能更优。

SELECT t.TenantID, t.TenantFName, t.TenantLName, u.UnitName,
       -- 近30天汇总
       sums.TotalDebit_Last30D,
       sums.HousingDebit_Last30D,
       sums.TotalCredit_Last30D,
       sums.HousingCredit_Last30D,
       -- 近30-60天汇总
       sums.TotalDebit_30To60D,
       sums.HousingDebit_30To60D,
       sums.TotalCredit_30To60D,
       sums.HousingCredit_30To60D,
       -- 近60-90天汇总
       sums.TotalDebit_60To90D,
       sums.HousingDebit_60To90D,
       sums.TotalCredit_60To90D,
       sums.HousingCredit_60To90D,
       -- 90天以前汇总
       sums.TotalDebit_Before90D,
       sums.HousingDebit_Before90D,
       sums.TotalCredit_Before90D,
       sums.HousingCredit_Before90D
FROM Tenants t
JOIN Units u ON t.UnitID = u.UnitID
LEFT JOIN (
    SELECT
        TenantID,
        -- 近30天指标
        SUM(CASE WHEN TransactionTypeID = 1 AND ChargeTypeID != 6 AND TenantTransactionDate >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) THEN TransactionAmount ELSE 0 END) AS TotalDebit_Last30D,
        SUM(CASE WHEN TransactionTypeID = 1 AND ChargeTypeID = 6 AND TenantTransactionDate >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) THEN TransactionAmount ELSE 0 END) AS HousingDebit_Last30D,
        SUM(CASE WHEN TransactionTypeID = 2 AND ChargeTypeID != 6 AND TenantTransactionDate >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) THEN TransactionAmount ELSE 0 END) AS TotalCredit_Last30D,
        SUM(CASE WHEN TransactionTypeID = 2 AND ChargeTypeID = 6 AND TenantTransactionDate >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) THEN TransactionAmount ELSE 0 END) AS HousingCredit_Last30D,
        -- 30-60天指标
        SUM(CASE WHEN TransactionTypeID = 1 AND ChargeTypeID != 6 AND TenantTransactionDate >= DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) AND TenantTransactionDate < DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) THEN TransactionAmount ELSE 0 END) AS TotalDebit_30To60D,
        SUM(CASE WHEN TransactionTypeID = 1 AND ChargeTypeID = 6 AND TenantTransactionDate >= DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) AND TenantTransactionDate < DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) THEN TransactionAmount ELSE 0 END) AS HousingDebit_30To60D,
        SUM(CASE WHEN TransactionTypeID = 2 AND ChargeTypeID != 6 AND TenantTransactionDate >= DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) AND TenantTransactionDate < DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) THEN TransactionAmount ELSE 0 END) AS TotalCredit_30To60D,
        SUM(CASE WHEN TransactionTypeID = 2 AND ChargeTypeID = 6 AND TenantTransactionDate >= DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) AND TenantTransactionDate < DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) THEN TransactionAmount ELSE 0 END) AS HousingCredit_30To60D,
        -- 60-90天指标
        SUM(CASE WHEN TransactionTypeID = 1 AND ChargeTypeID != 6 AND TenantTransactionDate >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY) AND TenantTransactionDate < DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) THEN TransactionAmount ELSE 0 END) AS TotalDebit_60To90D,
        SUM(CASE WHEN TransactionTypeID = 1 AND ChargeTypeID = 6 AND TenantTransactionDate >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY) AND TenantTransactionDate < DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) THEN TransactionAmount ELSE 0 END) AS HousingDebit_60To90D,
        SUM(CASE WHEN TransactionTypeID = 2 AND ChargeTypeID != 6 AND TenantTransactionDate >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY) AND TenantTransactionDate < DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) THEN TransactionAmount ELSE 0 END) AS TotalCredit_60To90D,
        SUM(CASE WHEN TransactionTypeID = 2 AND ChargeTypeID = 6 AND TenantTransactionDate >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY) AND TenantTransactionDate < DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) THEN TransactionAmount ELSE 0 END) AS HousingCredit_60To90D,
        -- 90天以前指标
        SUM(CASE WHEN TransactionTypeID = 1 AND ChargeTypeID != 6 AND TenantTransactionDate < DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY) THEN TransactionAmount ELSE 0 END) AS TotalDebit_Before90D,
        SUM(CASE WHEN TransactionTypeID = 1 AND ChargeTypeID = 6 AND TenantTransactionDate < DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY) THEN TransactionAmount ELSE 0 END) AS HousingDebit_Before90D,
        SUM(CASE WHEN TransactionTypeID = 2 AND ChargeTypeID != 6 AND TenantTransactionDate < DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY) THEN TransactionAmount ELSE 0 END) AS TotalCredit_Before90D,
        SUM(CASE WHEN TransactionTypeID = 2 AND ChargeTypeID = 6 AND TenantTransactionDate < DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY) THEN TransactionAmount ELSE 0 END) AS HousingCredit_Before90D
    FROM TenantTransactions
    GROUP BY TenantID
) sums ON sums.TenantID = t.TenantID
WHERE t.Prospect = 2
  AND t.PropertyID = 10

• 代码中日期计算以MySQL语法为例，其他数据库可替换对应日期函数：
  • SQL Server：将DATE_SUB(CURRENT_DATE, INTERVAL N DAY)替换为DATEADD(day, -N, GETDATE())
  • PostgreSQL：日期语法通用，直接使用即可
• 若需要将四个时段的结果拆分为独立行输出，可在子查询中使用UNION ALL分别聚合四个时段的结果，上述按列输出的方案更适合报表展示类场景，查询效率更高。

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