MySQL中基于WHERE NOT EXISTS批量补全每日缺失分组数据

在MySQL数据库中有两张表：

1. tb_group：记录每日分组访问次数，表结构及示例数据如下：

该表包含截至昨日的全年所有日期数据，其中_tdate为访问日期，_tnumber为对应分组的访问次数。

2. ta_group：存储系统内的全部分组信息。

需求：为tb_group中每一个已存在的日期，补全该日期下ta_group中存在但tb_group缺失的分组记录，缺失分组的_tnumber字段设为0。此前尝试的方案仅能处理单一日期，无法适配多日期场景。

通过生成日期与分组的全量组合，筛选出tb_group中未存在的记录并插入，具体SQL语句如下：

INSERT INTO tb_group (_tdate, _tgroup, _tnumber)
SELECT
    d._tdate,
    g._tgroup,
    0 AS _tnumber
FROM
    -- 提取tb_group中所有不重复的日期
    (SELECT DISTINCT _tdate FROM tb_group) d
    -- 与ta_group全部分组生成笛卡尔积，得到所有应存在的日期-分组组合
    CROSS JOIN ta_group g
WHERE
    -- 筛选出tb_group中尚未存在的日期-分组对
    NOT EXISTS (
        SELECT 1
        FROM tb_group t
        WHERE t._tdate = d._tdate
          AND t._tgroup = g._tgroup
    );

逻辑说明 #

1. 子查询(SELECT DISTINCT _tdate FROM tb_group)获取tb_group中所有已有的日期，确保覆盖所有需要补全的日期维度。
2. CROSS JOIN将所有日期与ta_group的全部分组进行组合，得到每个日期下应该包含的全部分组记录。
3. NOT EXISTS子句过滤掉tb_group中已存在的日期-分组组合，仅插入缺失的记录，且_tnumber固定为0。

注意事项 #

• 建议为tb_group表添加(_tdate, _tgroup)联合唯一索引，既能避免重复插入报错，也能提升查询和插入的效率。
• 若tb_group数据量较大，建议在业务低峰期执行该SQL，或分批处理，避免影响数据库性能。

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