获取各年月最小MyDate对应的ACCT10与BALANCE的SQL需求

需求说明 #

提取每个账户（ACCT10）对应年/月中最小的MyDate（该日期不一定是当月首日）对应的ACCT10、MyDate和BALANCE字段，且不能使用EXTRACT命令。

数据样本 #

ACCT10  MyDate      BALANCE
123     2023-01-05  1000
123     2023-01-02  950
456     2023-02-10  2000
456     2023-02-01  1800
789     2023-03-15  500
789     2023-03-03  450

期望输出 #

ACCT10  MyDate      BALANCE
123     2023-01-02  950
456     2023-02-01  1800
789     2023-03-03  450

修正后的SQL方案 #

通用兼容写法（适配多数关系型数据库） #

通过字符串截取获取年月维度，替代EXTRACT：

SELECT t.ACCT10, t.MyDate, t.BALANCE
FROM your_table t
INNER JOIN (
    SELECT 
        ACCT10,
        -- 将日期转为'YYYY-MM'格式的字符串作为年月分组依据
        LEFT(CAST(MyDate AS VARCHAR(10)), 7) AS year_month,
        MIN(MyDate) AS min_mydate
    FROM your_table
    GROUP BY ACCT10, LEFT(CAST(MyDate AS VARCHAR(10)), 7)
) AS sub
ON t.ACCT10 = sub.ACCT10
AND t.MyDate = sub.min_mydate

分数据库优化写法 #

MySQL/MariaDB

用DATE_FORMAT直接格式化日期为年月：

SELECT t.ACCT10, t.MyDate, t.BALANCE
FROM your_table t
INNER JOIN (
    SELECT 
        ACCT10,
        DATE_FORMAT(MyDate, '%Y-%m') AS year_month,
        MIN(MyDate) AS min_mydate
    FROM your_table
    GROUP BY ACCT10, DATE_FORMAT(MyDate, '%Y-%m')
) AS sub
ON t.ACCT10 = sub.ACCT10
AND t.MyDate = sub.min_mydate

SQL Server

用FORMAT或DATEFROMPARTS生成年月标识：

SELECT t.ACCT10, t.MyDate, t.BALANCE
FROM your_table t
INNER JOIN (
    SELECT 
        ACCT10,
        FORMAT(MyDate, 'yyyy-MM') AS year_month,
        MIN(MyDate) AS min_mydate
    FROM your_table
    GROUP BY ACCT10, FORMAT(MyDate, 'yyyy-MM')
) AS sub
ON t.ACCT10 = sub.ACCT10
AND t.MyDate = sub.min_mydate

逻辑说明 #

1. 子查询部分：按账户和年月分组，计算每组内的最小MyDate，通过字符串截取/日期格式化替代EXTRACT实现年月维度的分组
2. 主查询通过ACCT10和MyDate关联子查询结果，精准筛选出每个账户每个月最小日期对应的完整记录
3. 该方案避免了原SQL的语法错误，同时严格遵守不能使用EXTRACT的限制

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