求按年月、ID分组取最大日期的多条件累计求和SQL查询

嘿，我来帮你搞定这个需求！你已经有了基础的分组取最大日期的逻辑，现在要扩展到多条件场景的话，咱们用窗口函数就能轻松实现，既灵活又高效。

先明确下咱们的transaksi表结构：

• transid: 唯一交易ID
• idpinj: 借款标识（核心分组字段）
• tanggal: 交易日期
• sisapokok: 剩余本金
• sisajasa: 剩余利息

基础实现：按年月+ID分组取当月最新记录 #

首先，我们需要先筛选出每个idpinj在每个年月里的最新交易记录（最大tanggal的那条）。这里用ROW_NUMBER()窗口函数来标记每个分组内的最新记录：

WITH monthly_latest AS (
    SELECT 
        transid,
        idpinj,
        tanggal,
        sisapokok,
        sisajasa,
        -- 提取年月作为分组维度（不同数据库语法略有差异）
        -- MySQL: DATE_FORMAT(tanggal, '%Y-%m')
        -- PostgreSQL/Oracle: TO_CHAR(tanggal, 'YYYY-MM')
        -- SQL Server: FORMAT(tanggal, 'yyyy-MM')
        DATE_FORMAT(tanggal, '%Y-%m') AS year_month,
        -- 按idpinj+年月分区，按日期倒序排序，最新记录标记为1
        ROW_NUMBER() OVER (
            PARTITION BY idpinj, DATE_FORMAT(tanggal, '%Y-%m') 
            ORDER BY tanggal DESC
        ) AS row_rank
    FROM transaksi
    -- 这里可以先加全局筛选条件，比如日期范围、特定idpinj等
    -- 示例：WHERE tanggal BETWEEN '2018-01-01' AND '2018-03-31' AND idpinj IN (1,2)
)
SELECT 
    idpinj,
    year_month,
    tanggal AS latest_trans_date,
    sisapokok,
    sisajasa
FROM monthly_latest
WHERE row_rank = 1
ORDER BY idpinj, year_month;

扩展：加入累计求和逻辑 #

如果需要对筛选出的每月最新记录做累计求和（比如每个借款ID从第一个月到当前月的剩余本金/利息累计，或者全局所有借款的累计总和），只需要在主查询里加上SUM()窗口函数即可：

WITH monthly_latest AS (
    SELECT 
        transid,
        idpinj,
        tanggal,
        sisapokok,
        sisajasa,
        DATE_FORMAT(tanggal, '%Y-%m') AS year_month,
        ROW_NUMBER() OVER (
            PARTITION BY idpinj, DATE_FORMAT(tanggal, '%Y-%m') 
            ORDER BY tanggal DESC
        ) AS row_rank
    FROM transaksi
    -- 第一步筛选：比如只看2018年的交易
    WHERE YEAR(tanggal) = 2018
)
SELECT 
    idpinj,
    year_month,
    tanggal AS latest_trans_date,
    sisapokok,
    sisajasa,
    -- 按借款ID单独累计：每个idpinj的本金/利息从年初到当前月的累计
    SUM(sisapokok) OVER (PARTITION BY idpinj ORDER BY year_month) AS cumulative_pokok_per_id,
    SUM(sisajasa) OVER (PARTITION BY idpinj ORDER BY year_month) AS cumulative_jasa_per_id,
    -- 全局累计：所有借款ID的本金/利息从年初到当前月的总累计
    SUM(sisapokok) OVER (ORDER BY year_month) AS total_cumulative_pokok,
    SUM(sisajasa) OVER (ORDER BY year_month) AS total_cumulative_jasa
FROM monthly_latest
WHERE row_rank = 1
-- 第二步筛选：比如只看idpinj=1的记录
-- AND idpinj = 1
ORDER BY idpinj, year_month;

多条件筛选的灵活添加 #

你可以根据需求在两个地方添加筛选条件：

1. CTE内部：在FROM transaksi后面加WHERE，用于过滤原始数据（比如日期范围、特定借款ID、剩余金额阈值等）
   • 示例：WHERE idpinj IN (1,3) AND sisapokok > 500
2. 主查询内部：在WHERE row_rank = 1后面加额外条件，用于过滤已经筛选出的每月最新记录
   • 示例：AND year_month >= '2018-02'

注意事项 #

• 不同数据库的日期格式化语法略有差异，记得根据自己使用的数据库调整（代码里已经标注了不同数据库的写法）
• 如果你的数据库不支持CTE（比如MySQL 5.6及以前），可以用子查询替代CTE的写法
• 确保tanggal字段是日期类型，避免字符串排序导致的错误

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