如何用SQL按日期计算差值并自动更新pc12_totals表？

嘿，我来帮你搞定这个SQL更新的问题！首先得明确一个前提：我假设你的pc12_totals表是用来按日期存储每日hobbs差值汇总的，结构大概是这样（如果你的表结构不同，可以对应调整字段名）：

CREATE TABLE pc12_totals (
    flight_date date PRIMARY KEY,
    hobbs_total decimal(5,2) NOT NULL
);

接下来分两种场景给你解决方案：一次性批量更新现有数据，以及自动同步（新增/修改/删除记录时自动更新totals表）。

一、一次性批量更新现有数据 #

如果只是想把pc12_status里已有的数据按日期计算差值后更新到pc12_totals，用INNER JOIN配合子查询就可以：

UPDATE pc12_totals t
-- 关联子查询，按日期计算每日的hobbs差值总和
INNER JOIN (
    SELECT 
        flight_date,
        -- 这里用SUM是考虑到同一个日期可能有多条记录，如果单日期只有一条，直接写hobbs_end - hobbs_start就行
        SUM(hobbs_end - hobbs_start) AS daily_hobbs_diff
    FROM pc12_status
    GROUP BY flight_date
) s ON t.flight_date = s.flight_date
-- 更新totals表的对应字段
SET t.hobbs_total = s.daily_hobbs_diff;

关于JOIN类型的说明： #

这里用INNER JOIN是因为它只会匹配两张表中都存在的flight_date记录，也就是只更新pc12_totals里已经有的日期数据。如果你的pc12_totals可能没有某些日期的记录，想要同时插入新日期的数据，可以用INSERT ... ON DUPLICATE KEY UPDATE语句：

-- 插入新日期的差值，已存在的日期则更新差值
INSERT INTO pc12_totals (flight_date, hobbs_total)
SELECT 
    flight_date,
    SUM(hobbs_end - hobbs_start) AS daily_hobbs_diff
FROM pc12_status
GROUP BY flight_date
ON DUPLICATE KEY UPDATE hobbs_total = VALUES(hobbs_total);

二、自动同步数据（新增/修改/删除时自动更新） #

如果想要每次pc12_status的数据变化时，pc12_totals自动更新，那可以用数据库触发器来实现：

1. 插入/更新记录时的触发器 #

当pc12_status新增或修改记录时，自动同步差值到pc12_totals：

DELIMITER //
CREATE TRIGGER sync_totals_after_insert_update
AFTER INSERT ON pc12_status
FOR EACH ROW
BEGIN
    -- 先尝试更新，没有对应日期则插入新记录
    INSERT INTO pc12_totals (flight_date, hobbs_total)
    VALUES (NEW.flight_date, NEW.hobbs_end - NEW.hobbs_start)
    ON DUPLICATE KEY UPDATE hobbs_total = hobbs_total + (NEW.hobbs_end - NEW.hobbs_start);
    
    -- 如果是修改记录的情况，需要先减去旧值再加新值，可调整为：
    -- INSERT INTO pc12_totals (flight_date, hobbs_total)
    -- VALUES (NEW.flight_date, (SELECT COALESCE(hobbs_total, 0) FROM pc12_totals WHERE flight_date = NEW.flight_date) - (OLD.hobbs_end - OLD.hobbs_start) + (NEW.hobbs_end - NEW.hobbs_start))
    -- ON DUPLICATE KEY UPDATE hobbs_total = VALUES(hobbs_total);
END //
DELIMITER ;

2. 删除记录时的触发器 #

当pc12_status删除记录时，重新计算对应日期的差值并更新：

DELIMITER //
CREATE TRIGGER sync_totals_after_delete
AFTER DELETE ON pc12_status
FOR EACH ROW
BEGIN
    -- 重新计算该日期的总差值
    UPDATE pc12_totals t
    INNER JOIN (
        SELECT flight_date, SUM(hobbs_end - hobbs_start) AS daily_hobbs_diff
        FROM pc12_status
        WHERE flight_date = OLD.flight_date
        GROUP BY flight_date
    ) s ON t.flight_date = s.flight_date
    SET t.hobbs_total = s.daily_hobbs_diff;
    
    -- 可选：如果删除后该日期没有任何记录了，可以删除totals里的对应行
    -- DELETE FROM pc12_totals WHERE flight_date = OLD.flight_date AND NOT EXISTS (SELECT 1 FROM pc12_status WHERE flight_date = OLD.flight_date);
END //
DELIMITER ;

关键知识点总结 #

• 减法运算：直接用hobbs_end - hobbs_start即可，因为两个字段都是decimal类型，运算结果也会保持精度。
• JOIN选择：INNER JOIN适合匹配已有记录的更新；如果需要覆盖所有日期（包括新增），INSERT ... ON DUPLICATE KEY UPDATE更高效。
• 分组处理：如果同一个flight_date有多条记录，一定要用SUM()来累加差值，否则只会取单条记录的结果。

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