MySQL按年统计damage、policy表的记录数与字段值总和

你提供的代码存在以下可直接导致执行失败/结果错误的问题：

• 测试数据插入语句存在表名笔误：insert into steta values(9, 22, 2021);中steta为拼写错误，实际应为damage
• 查询语句不完整：仅写了SELECT字段部分，缺少FROM、表关联、GROUP BY分组子句，无法直接执行
• 直接关联两张原始表会产生笛卡尔积：同一年度下damage表的N条记录会和policy表的M条记录交叉生成N*M条结果，最终count和sum的结果会远大于实际值
• 年度字段未对齐：两张表存储年度的字段分别为year1和year2，仅取year1会导致年度匹配逻辑错误

因为两张表的统计维度独立（损坏记录属于damage表、保单属于policy表），需要先分别对两张表按年度做聚合计算，再按年度关联合并结果，避免笛卡尔积问题。如果需要固定返回2019、2020、2021三个年度（哪怕某年度无数据也返回0值），可以先生成年度基准表再左连两个聚合结果。

完整可执行代码如下：

-- 1. 建表语句（原建表逻辑无问题，保留）
CREATE TABLE damage(
 damageID MEDIUMINT NOT NULL AUTO_INCREMENT,
 damage_cost INT(10),
 year1 YEAR,
 PRIMARY KEY (damageID)
);

CREATE TABLE policy(
policyID MEDIUMINT NOT NULL AUTO_INCREMENT,
profit INT(10),
year2 YEAR,
PRIMARY KEY (policyID)
);

-- 2. 测试数据插入（已修正表名笔误）
INSERT INTO damage VALUES(1, 1000, 2019);
INSERT INTO damage VALUES(2, 200, 2019);
INSERT INTO damage VALUES(3, 100, 2019);

INSERT INTO damage VALUES(4, 10, 2020);
INSERT INTO damage VALUES(5, 400, 2020);
INSERT INTO damage VALUES(6, 800, 2020);

INSERT INTO damage VALUES(7, 12, 2021);
INSERT INTO damage VALUES(8, 55, 2021);
INSERT INTO damage VALUES(9, 22, 2021); -- 原笔误steta已修正为damage

INSERT INTO policy VALUES(1, 5, 2019);
INSERT INTO policy VALUES(2, 23, 2019);
INSERT INTO policy VALUES(3, 99, 2019);

INSERT INTO policy VALUES(4, 510, 2020);
INSERT INTO policy VALUES(5, 35, 2020);
INSERT INTO policy VALUES(6, 52, 2020);

INSERT INTO policy VALUES(7, 4, 2021);
INSERT INTO policy VALUES(8, 23, 2021);
INSERT INTO policy VALUES(9, 198, 2021);

-- 3. 统计查询语句
SELECT 
    t.year,
    IFNULL(d.damage_count, 0) AS NumberOfDamages,
    IFNULL(d.damage_cost_sum, 0) AS sumOfDamages,
    IFNULL(p.policy_count, 0) AS NumberOfPolicies,
    IFNULL(p.profit_sum, 0) AS sumOfProfits
FROM (
    -- 生成指定统计年度的基准表，保证年度不缺失
    SELECT 2019 AS year UNION ALL
    SELECT 2020 UNION ALL
    SELECT 2021
) t
LEFT JOIN (
    -- 按年度聚合损坏表统计值
    SELECT 
        year1 AS year,
        COUNT(damageID) AS damage_count,
        SUM(damage_cost) AS damage_cost_sum
    FROM damage
    WHERE year1 IN (2019,2020,2021)
    GROUP BY year1
) d ON t.year = d.year
LEFT JOIN (
    -- 按年度聚合保单表统计值
    SELECT 
        year2 AS year,
        COUNT(policyID) AS policy_count,
        SUM(profit) AS profit_sum
    FROM policy
    WHERE year2 IN (2019,2020,2021)
    GROUP BY year2
) p ON t.year = p.year
ORDER BY t.year;

基于你提供的测试数据，执行后返回结果如下：

补充说明：如果不需要固定返回三个年度，只要返回两张表中存在的所有年度的统计结果，可以去掉年度基准表，直接将两个聚合子查询通过ON d.year = p.year关联，用COALESCE(d.year, p.year)取年度值即可。

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