MYSQL 基于关联表字段计算结果向目标表插入唯一记录的实现方法

原有统计SQL修正 #

• 你现有SQL的GROUP BY中包含了ep.buckets字段，会导致按单条Buckets记录分组，无法正确按lot、crewid维度汇总Buckets总数，需要先去掉该分组字段。
• 业务规则要求取除法结果的整数部分，用FLOOR()函数比ROUND()更匹配需求，避免四舍五入多生成不符合要求的Bin记录。

MySQL 8.0+ 实现方案 #

用递归CTE生成连续数字序列，关联统计结果生成对应条数的待插入数据：

INSERT INTO Production (LOT)
WITH RECURSIVE num_sequence AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM num_sequence WHERE n < 1000 -- 可根据最大可能Bin数调整上限
)
SELECT stat.lot
FROM (
    -- 修正后的统计SQL
    SELECT FLOOR(SUM(ep.Buckets)/30) AS bin_count, ep.lot
    FROM On_EmpProdDetails ep
    GROUP BY ep.lot, ep.crewid
) stat
JOIN num_sequence ns ON ns.n <= stat.bin_count;

因为Production表的BinID是自增字段，插入时不需要显式赋值，数据库会自动生成连续的唯一BinID，完全符合预期结果。

MySQL 5.x 实现方案 #

低版本MySQL不支持递归CTE，可以通过笛卡尔积生成临时数字序列实现：

INSERT INTO Production (LOT)
SELECT stat.lot
FROM (
    SELECT FLOOR(SUM(ep.Buckets)/30) AS bin_count, ep.lot
    FROM On_EmpProdDetails ep
    GROUP BY ep.lot, ep.crewid
) stat
JOIN (
    -- 生成1~1000的连续数字序列，可根据需求扩展
    SELECT a.N + b.N * 10 + c.N * 100 + 1 AS n
    FROM (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
    CROSS JOIN (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    CROSS JOIN (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
) ns ON ns.n <= stat.bin_count;

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