SQL需求：按ID和月份去重并生成每月单条记录

我来帮你搞定这个需求——要实现按ID和月份去重，每个月留一条记录，还得补全中间缺失的月份，用前一个有效月份的Name和Age填充对吧？下面我会一步步拆解思路，再给出具体的SQL代码示例（以SQL Server和MySQL 8.0+为例）。

核心思路拆解 #

我们可以分成三个关键步骤来实现：

1. 抓取每个ID+月份的最新记录：如果同一个ID在同一个月有多条数据，我们只保留日期最晚的那一条，作为该月份的基准数据。
2. 生成每个ID的连续月份序列：覆盖该ID从最早出现的月份到最晚出现的月份之间的所有月份，这样就能把缺失的月份补出来。
3. 关联填充缺失数据：把连续月份序列和基准数据关联，用前一个有效月份的Name和Age来填充缺失月份的信息，同时处理日期显示。

SQL Server 实现示例 #

首先先创建测试表（方便你验证效果）：

CREATE TABLE #TestData (
    ID INT,
    Date DATE,
    Name VARCHAR(50),
    Age INT
);

INSERT INTO #TestData VALUES
(1, '2015-04-10', 'Theja', 24),
(1, '2015-04-28', 'Theja1', 26),
(1, '2015-07-14', 'Theja2', 45),
(1, '2015-07-30', 'Theja2', 45),
(1, '2015-08-30', 'Theja3', 54),
(2, '2016-04-10', 'Jaya', 23),
(2, '2016-04-28', 'Jaya', 23),
(2, '2016-05-14', 'Jaya1', 65),
(2, '2016-05-30', 'Jaya1', 65);

然后是核心查询：

WITH MonthlyLatest AS (
    -- 第一步：获取每个ID+月份的最新记录
    SELECT 
        ID,
        DATEFROMPARTS(YEAR(Date), MONTH(Date), 1) AS MonthStart,
        MAX(Date) AS LatestDate,
        Name,
        Age
    FROM #TestData
    GROUP BY ID, YEAR(Date), MONTH(Date), Name, Age
    -- 这里假设同一月份的Name和Age是一致的，如果有不一致，后面会补充处理方式
),
IDMonthSequence AS (
    -- 第二步：递归生成每个ID的连续月份序列
    SELECT 
        ID,
        MIN(MonthStart) AS MinMonth,
        MAX(MonthStart) AS MaxMonth
    FROM MonthlyLatest
    GROUP BY ID
    UNION ALL
    SELECT 
        ID,
        DATEADD(MONTH, 1, MinMonth) AS MinMonth,
        MaxMonth
    FROM IDMonthSequence
    WHERE DATEADD(MONTH, 1, MinMonth) <= MaxMonth
),
FilledData AS (
    -- 第三步：关联并填充缺失数据
    SELECT 
        s.ID,
        -- 有数据的月份用该月最新日期，缺失月份用当月第一天
        CASE 
            WHEN ml.LatestDate IS NOT NULL THEN ml.LatestDate 
            ELSE DATEADD(DAY, 0, s.MinMonth) 
        END AS Date,
        -- 用LAST_VALUE向前填充Name和Age
        LAST_VALUE(ml.Name) OVER (
            PARTITION BY s.ID 
            ORDER BY s.MinMonth 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS Name,
        LAST_VALUE(ml.Age) OVER (
            PARTITION BY s.ID 
            ORDER BY s.MinMonth 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS Age
    FROM IDMonthSequence s
    LEFT JOIN MonthlyLatest ml ON s.ID = ml.ID AND s.MinMonth = ml.MonthStart
)
-- 格式化日期为dd/MM/yyyy格式，排序输出
SELECT 
    ID, 
    FORMAT(Date, 'dd/MM/yyyy') AS Date, 
    Name, 
    Age
FROM FilledData
ORDER BY ID, Date;

MySQL 8.0+ 实现示例 #

如果用MySQL，代码逻辑类似，只是日期函数和语法略有不同：

-- 先创建测试表
CREATE TABLE TestData (
    ID INT,
    Date DATE,
    Name VARCHAR(50),
    Age INT
);

INSERT INTO TestData VALUES
(1, '2015-04-10', 'Theja', 24),
(1, '2015-04-28', 'Theja1', 26),
(1, '2015-07-14', 'Theja2', 45),
(1, '2015-07-30', 'Theja2', 45),
(1, '2015-08-30', 'Theja3', 54),
(2, '2016-04-10', 'Jaya', 23),
(2, '2016-04-28', 'Jaya', 23),
(2, '2016-05-14', 'Jaya1', 65),
(2, '2016-05-30', 'Jaya1', 65);

核心查询：

WITH RECURSIVE IDMonthSequence AS (
    -- 生成每个ID的连续月份序列
    SELECT 
        ID,
        DATE_FORMAT(MIN(Date), '%Y-%m-01') AS MonthStart,
        DATE_FORMAT(MAX(Date), '%Y-%m-01') AS MaxMonth
    FROM TestData
    GROUP BY ID
    UNION ALL
    SELECT 
        ID,
        DATE_ADD(MonthStart, INTERVAL 1 MONTH),
        MaxMonth
    FROM IDMonthSequence
    WHERE DATE_ADD(MonthStart, INTERVAL 1 MONTH) <= MaxMonth
),
MonthlyLatest AS (
    -- 获取每个ID+月份的最新记录
    SELECT 
        ID,
        DATE_FORMAT(Date, '%Y-%m-01') AS MonthStart,
        MAX(Date) AS LatestDate,
        Name,
        Age
    FROM TestData
    GROUP BY ID, DATE_FORMAT(Date, '%Y-%m-01'), Name, Age
)
-- 关联填充并输出
SELECT 
    s.ID,
    DATE_FORMAT(
        IF(ml.LatestDate IS NOT NULL, ml.LatestDate, s.MonthStart), 
        '%d/%m/%Y'
    ) AS Date,
    LAST_VALUE(ml.Name) OVER (
        PARTITION BY s.ID 
        ORDER BY s.MonthStart 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Name,
    LAST_VALUE(ml.Age) OVER (
        PARTITION BY s.ID 
        ORDER BY s.MonthStart 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Age
FROM IDMonthSequence s
LEFT JOIN MonthlyLatest ml ON s.ID = ml.ID AND s.MonthStart = ml.MonthStart
ORDER BY s.ID, s.MonthStart;

特殊情况处理 #

如果你的数据中存在同一ID同一月份但Name/Age不同的情况，上面的分组逻辑可能会出问题，这时候可以用ROW_NUMBER()来取最新的那条记录：

-- 以SQL Server为例，修改MonthlyLatest部分
WITH RankedData AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY ID, YEAR(Date), MONTH(Date) 
            ORDER BY Date DESC
        ) AS rn
    FROM #TestData
),
MonthlyLatest AS (
    SELECT ID, Date, Name, Age
    FROM RankedData
    WHERE rn = 1
)
-- 后面的IDMonthSequence和FilledData部分和之前一样，只需把MonthlyLatest替换成这个即可

这样就能确保每个月份只保留最新的那条记录，不管Name和Age有没有变化。

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