如何查询时态表字段变更记录并保留中间NULL值不丢失

你需要用**孤岛分组（连续相同值分组）**的思路实现，核心是给连续出现的相同Number（包括连续NULL）分配独立分组ID，避免跨时间段的相同值被误合并：

通用版SQL（支持IS NOT DISTINCT FROM语法的数据库，如PostgreSQL、SQL Server 2022+、MySQL 8.0.13+） #

WITH ranked_data AS (
    -- 按版本时间倒序排列，取上一行的Number值判断是否发生变更
    SELECT 
        Number,
        VersionStartDate,
        LAG(Number) OVER (ORDER BY VersionStartDate DESC) AS prev_Number
    FROM _TABLE_
),
grouped_data AS (
    -- 遇到值变更则新增分组ID，连续相同值共享同一个分组ID
    SELECT 
        Number,
        VersionStartDate,
        SUM(CASE WHEN Number IS NOT DISTINCT FROM prev_Number THEN 0 ELSE 1 END) 
            OVER (ORDER BY VersionStartDate DESC ROWS UNBOUNDED PRECEDING) AS group_id
    FROM ranked_data
)
-- 按分组聚合，取每个分组的最小时间即该次变更的起始时间
SELECT 
    Number,
    MIN(VersionStartDate) AS VersionStartDate
FROM grouped_data
GROUP BY group_id, Number
ORDER BY VersionStartDate DESC;

低版本数据库兼容版SQL #

如果你的数据库不支持IS NOT DISTINCT FROM语法，可以用COALESCE把NULL替换成业务中不会出现的特殊值做等值判断：

WITH ranked_data AS (
    SELECT 
        Number,
        VersionStartDate,
        LAG(Number) OVER (ORDER BY VersionStartDate DESC) AS prev_Number
    FROM _TABLE_
),
grouped_data AS (
    SELECT 
        Number,
        VersionStartDate,
        SUM(CASE WHEN COALESCE(Number, '-999999') = COALESCE(prev_Number, '-999999') THEN 0 ELSE 1 END) 
            OVER (ORDER BY VersionStartDate DESC ROWS UNBOUNDED PRECEDING) AS group_id
    FROM ranked_data
)
SELECT 
    Number,
    MIN(VersionStartDate) AS VersionStartDate
FROM grouped_data
GROUP BY group_id, Number
ORDER BY VersionStartDate DESC;

实现原理 #

1. 按时间倒序遍历所有记录，每次遇到Number发生变化（包括非NULL和NULL的切换、不同非NULL值切换）就生成新的分组ID
2. 连续相同的Number（包括连续出现的NULL）会被分到同一个组，跨时间段出现的相同值/NULL会被分到不同的组，解决了原有GROUP BY合并所有NULL的问题
3. 每个组取最小的VersionStartDate，就是该次Number变更的起始时间

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