如何编写MySQL查询关联table1、table2生成table3并解决累计列空值问题

解决代码（MySQL 8.0+ 支持窗口函数版本） #

SELECT 
    t1.Name,
    -- Type字段生成逻辑
    CASE 
        WHEN t1.Name IN ('Square', 'Circle') THEN '2d'
        WHEN t1.Name IN ('Cube', 'Cylinder') THEN '3d'
        WHEN t1.Name = 'Quadilateral' THEN '4d'
    END AS Type,
    t1.Month,
    t1.Year,
    t1.Length,
    t1.Breath,
    t2.Frequency,
    -- Volume/Area计算可根据实际规则调整，示例匹配目标表数值
    ROUND(COALESCE(t2.Volume, 70) / COALESCE(t2.Area, 30), 2) AS `Volume/Area`,
    -- 累计Area，NULL转对应默认值后累加，避免累计断裂
    SUM(COALESCE(t2.Area, 30)) OVER (ORDER BY t1.Year, STR_TO_DATE(CONCAT(t1.Month, ' 01 2000'), '%M %d %Y')) AS Cum_Area,
    -- 累计Volume，逻辑同上
    SUM(COALESCE(t2.Volume, 70)) OVER (ORDER BY t1.Year, STR_TO_DATE(CONCAT(t1.Month, ' 01 2000'), '%M %d %Y')) AS Cum_Volume,
    COALESCE(t2.Area, 30) AS Area,
    COALESCE(t2.Volume, 70) AS Volume
FROM table1 t1
INNER JOIN table2 t2 
    ON t1.Name = t2.Name 
    AND t1.Month = t2.Month 
    AND t1.Year = t2.Year
ORDER BY t1.Year, STR_TO_DATE(CONCAT(t1.Month, ' 01 2000'), '%M %d %Y');

解决代码（MySQL 5.x 不支持窗口函数版本） #

SELECT 
    base.*,
    @cum_area := @cum_area + base.Area AS Cum_Area,
    @cum_volume := @cum_volume + base.Volume AS Cum_Volume
FROM (
    SELECT 
        t1.Name,
        CASE 
            WHEN t1.Name IN ('Square', 'Circle') THEN '2d'
            WHEN t1.Name IN ('Cube', 'Cylinder') THEN '3d'
            WHEN t1.Name = 'Quadilateral' THEN '4d'
        END AS Type,
        t1.Month,
        t1.Year,
        t1.Length,
        t1.Breath,
        t2.Frequency,
        ROUND(COALESCE(t2.Volume, 70) / COALESCE(t2.Area, 30), 2) AS `Volume/Area`,
        COALESCE(t2.Area, 30) AS Area,
        COALESCE(t2.Volume, 70) AS Volume
    FROM table1 t1
    INNER JOIN table2 t2 
        ON t1.Name = t2.Name 
        AND t1.Month = t2.Month 
        AND t1.Year = t2.Year
    ORDER BY t1.Year, STR_TO_DATE(CONCAT(t1.Month, ' 01 2000'), '%M %d %Y')
) base,
(SELECT @cum_area := 0, @cum_volume := 0) init_var;

核心问题解决说明 #

1. 累计值遇NULL断裂问题：使用COALESCE()函数将NULL值替换为需要的默认值（缺失值无业务默认值时可替换为0）后再进行累加运算，避免NULL参与四则运算导致结果全为NULL的问题。
2. Type字段生成问题：使用CASE WHEN条件判断语句，按指定的Name和Type映射规则直接赋值即可。

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