如何按日期合并行？获取协议每日编辑后的最新状态

问题场景 #

你有一张追踪协议每一次编辑操作的表，每次编辑都会新增一行数据——未修改的字段会填充为NULL。现在需要合并同一协议在同一天内的所有变更，得到该协议当日完成所有修改后的最终状态。

原始操作记录 #

期望的当日最终状态 #

解决方案：SQL实现 #

核心逻辑是：对每个协议（ID）和日期（仅保留年月日部分），为每个字段取当日最后一次非NULL的修改值。以下提供两种适配不同数据库环境的实现方式：

方法1：窗口函数方案（适用于PostgreSQL、SQL Server、MySQL 8.0+等支持窗口函数的数据库）

通过ROW_NUMBER()为每个字段的有效修改记录排序，再聚合提取最新值：

WITH ranked_changes AS (
    SELECT
        DATE(Date) AS record_date,
        ID,
        Period,
        ArchivedBy,
        ArchivedFlag,
        Value,
        -- 为每个字段的非NULL记录按时间倒序排名
        ROW_NUMBER() OVER (
            PARTITION BY ID, DATE(Date) 
            ORDER BY CASE WHEN Period IS NOT NULL THEN Date ELSE '1900-01-01' END DESC
        ) AS rn_period,
        ROW_NUMBER() OVER (
            PARTITION BY ID, DATE(Date) 
            ORDER BY CASE WHEN ArchivedBy IS NOT NULL THEN Date ELSE '1900-01-01' END DESC
        ) AS rn_archivedby,
        ROW_NUMBER() OVER (
            PARTITION BY ID, DATE(Date) 
            ORDER BY CASE WHEN ArchivedFlag IS NOT NULL THEN Date ELSE '1900-01-01' END DESC
        ) AS rn_archivedflag,
        ROW_NUMBER() OVER (
            PARTITION BY ID, DATE(Date) 
            ORDER BY CASE WHEN Value IS NOT NULL THEN Date ELSE '1900-01-01' END DESC
        ) AS rn_value
    FROM your_table_name
)
SELECT
    record_date AS Date,
    ID,
    MAX(CASE WHEN rn_period = 1 THEN Period END) AS Period,
    MAX(CASE WHEN rn_archivedby = 1 THEN ArchivedBy END) AS ArchivedBy,
    MAX(CASE WHEN rn_archivedflag = 1 THEN ArchivedFlag END) AS ArchivedFlag,
    MAX(CASE WHEN rn_value = 1 THEN Value END) AS Value
FROM ranked_changes
GROUP BY record_date, ID;

方法2：子查询聚合方案（兼容性更强，适用于多数旧版数据库）

针对每个字段单独查询当日最后一次有效修改的值，再组合成最终结果：

SELECT
    DATE(t.Date) AS Date,
    t.ID,
    -- 获取当日Period字段最后一次非NULL的值
    (SELECT Period 
     FROM your_table_name 
     WHERE ID = t.ID AND DATE(Date) = DATE(t.Date) AND Period IS NOT NULL 
     ORDER BY Date DESC LIMIT 1) AS Period,
    -- 获取当日ArchivedBy字段最后一次非NULL的值
    (SELECT ArchivedBy 
     FROM your_table_name 
     WHERE ID = t.ID AND DATE(Date) = DATE(t.Date) AND ArchivedBy IS NOT NULL 
     ORDER BY Date DESC LIMIT 1) AS ArchivedBy,
    -- 获取当日ArchivedFlag字段最后一次非NULL的值
    (SELECT ArchivedFlag 
     FROM your_table_name 
     WHERE ID = t.ID AND DATE(Date) = DATE(t.Date) AND ArchivedFlag IS NOT NULL 
     ORDER BY Date DESC LIMIT 1) AS ArchivedFlag,
    -- 获取当日Value字段最后一次非NULL的值
    (SELECT Value 
     FROM your_table_name 
     WHERE ID = t.ID AND DATE(Date) = DATE(t.Date) AND Value IS NOT NULL 
     ORDER BY Date DESC LIMIT 1) AS Value
FROM your_table_name t
GROUP BY DATE(t.Date), t.ID;

关键说明 #

• 两种方案都会忽略时间的时分秒部分，确保同一自然日的所有操作被合并。
• 如果某个字段在当日没有任何修改（即所有行该字段都是NULL），结果中该字段会保留NULL——如果需要继承之前日期的状态，需要额外处理历史数据的合并逻辑。

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