SQL补全缺失值：为所有日期补全days_num至指定值并设value为NULL

当然可以通过SQL查询实现这个需求！核心思路是先构建出所有需要的日期+天数组合，再和原表做左连接，这样缺失的value就会自动填充为NULL。

核心逻辑拆解 #

我们需要确保每个date都对应days_num从0到5的所有值，所以要先生成两个基础数据集：

• 从原表中提取所有不重复的日期
• 生成0到5的连续数字序列
  然后通过交叉连接得到完整的日期-天数组合，最后左连接原表匹配已有数据，缺失项自然会显示NULL。

不同数据库的具体实现 #

下面针对几种常用数据库给出可直接运行的SQL代码：

1. PostgreSQL

PostgreSQL自带的generate_series函数能快速生成数字序列：

WITH all_dates AS (
    SELECT DISTINCT date FROM your_table
),
all_days AS (
    SELECT generate_series(0,5) AS days_num
)
SELECT 
    ad.date,
    ady.days_num,
    t.value
FROM all_dates ad
CROSS JOIN all_days ady
LEFT JOIN your_table t 
    ON ad.date = t.date 
    AND ady.days_num = t.days_num
ORDER BY ad.date, ady.days_num;

2. MySQL 8.0+

MySQL 8.0及以上支持递归CTE生成连续序列：

WITH RECURSIVE all_days AS (
    SELECT 0 AS days_num
    UNION ALL
    SELECT days_num + 1 FROM all_days WHERE days_num < 5
),
all_dates AS (
    SELECT DISTINCT date FROM your_table
)
SELECT 
    ad.date,
    ady.days_num,
    t.value
FROM all_dates ad
CROSS JOIN all_days ady
LEFT JOIN your_table t 
    ON ad.date = t.date 
    AND ady.days_num = t.days_num
ORDER BY ad.date, ady.days_num;

3. SQL Server

可以用值列表直接生成需要的天数序列：

WITH all_days AS (
    SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5)) AS nums(n)
),
all_dates AS (
    SELECT DISTINCT date FROM your_table
)
SELECT 
    ad.date,
    ady.n AS days_num,
    t.value
FROM all_dates ad
CROSS JOIN all_days ady
LEFT JOIN your_table t 
    ON ad.date = t.date 
    AND ady.n = t.days_num
ORDER BY ad.date, ady.n;

执行结果示例 #

运行上述SQL后，就能得到你期望的补全结果：

date       | days_num | value
2023-01-01 | 0        | 2
2023-01-01 | 1        | 3
2023-01-01 | 2        | 4
2023-01-01 | 3        | 4
2023-01-01 | 4        | 2
2023-01-01 | 5        | 1

2023-01-02 | 0        | 2
2023-01-02 | 1        | 3
2023-01-02 | 2        | 4
2023-01-02 | 3        | 2
2023-01-02 | 4        | 2
2023-01-02 | 5        | NULL

2023-01-03 | 0        | 3
2023-01-03 | 1        | 4
2023-01-03 | 2        | 5
2023-01-03 | 3        | NULL
2023-01-03 | 4        | NULL
2023-01-03 | 5        | NULL

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