SQL查询聚合：如何获取当月日数值最大的记录及对应金额

你的核心需求是从dd.mm.yyyy格式的日期字符串里，提取日部分的数值，找到每个账户当月日数值最大的记录，进而获取对应金额。下面分不同SQL方言给出具体实现方案：

方案一：子查询关联（通用思路） #

先通过子查询算出每个账户、每个年月对应的最大日数值，再关联原表匹配符合条件的记录。

MySQL 实现 #

SELECT t.Id, t.Account, t.Date, t.Amount
FROM transactions t
JOIN (
    SELECT 
        Account,
        SUBSTRING_INDEX(Date, '.', -2) AS month_year, -- 提取mm.yyyy确定所属年月
        MAX(CAST(SUBSTRING_INDEX(Date, '.', 1) AS UNSIGNED)) AS max_day
    FROM transactions
    GROUP BY Account, month_year
) AS max_days 
ON t.Account = max_days.Account
AND SUBSTRING_INDEX(t.Date, '.', -2) = max_days.month_year
AND CAST(SUBSTRING_INDEX(t.Date, '.', 1) AS UNSIGNED) = max_days.max_day;

SQL Server 实现 #

SELECT t.Id, t.Account, t.Date, t.Amount
FROM transactions t
JOIN (
    SELECT 
        Account,
        RIGHT(Date, 7) AS month_year, -- 提取mm.yyyy
        MAX(CAST(LEFT(Date, 2) AS INT)) AS max_day
    FROM transactions
    GROUP BY Account, month_year
) AS max_days 
ON t.Account = max_days.Account
AND RIGHT(t.Date, 7) = max_days.month_year
AND CAST(LEFT(t.Date, 2) AS INT) = max_days.max_day;

PostgreSQL 实现 #

SELECT t.Id, t.Account, t.Date, t.Amount
FROM transactions t
JOIN (
    SELECT 
        Account,
        CONCAT(SPLIT_PART(Date, '.', 2), '.', SPLIT_PART(Date, '.', 3)) AS month_year,
        MAX(CAST(SPLIT_PART(Date, '.', 1) AS INT)) AS max_day
    FROM transactions
    GROUP BY Account, month_year
) AS max_days 
ON t.Account = max_days.Account
AND CONCAT(SPLIT_PART(t.Date, '.', 2), '.', SPLIT_PART(t.Date, '.', 3)) = max_days.month_year
AND CAST(SPLIT_PART(t.Date, '.', 1) AS INT) = max_days.max_day;

方案二：窗口函数（更简洁） #

用窗口函数按账户+年月分区，按日数值降序排名，直接取排名第一的记录，适合需要处理同账户同月多个最大日记录的场景（可控制返回条数）。

MySQL 实现 #

SELECT Id, Account, Date, Amount
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY Account, SUBSTRING_INDEX(Date, '.', -2) 
            ORDER BY CAST(SUBSTRING_INDEX(Date, '.', 1) AS UNSIGNED) DESC
        ) AS rn
    FROM transactions
) AS ranked
WHERE rn = 1;

如果允许返回同账户同月所有最大日的记录，可把ROW_NUMBER()换成RANK()。

注：上述代码中假设你的表名为transactions，如果实际表名不同，自行替换即可。

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