如何自动更新streets表total_buildings为对应街道的房屋统计数？

第一步：初始化现有数据 #

先把streets表中已有的街道对应的total_buildings填充完成，使用UPDATE结合子查询实现：

UPDATE streets s
SET total_buildings = (
    -- 若同一条街道内house_no不会重复，直接用COUNT(house_no)即可
    SELECT COUNT(DISTINCT house_no)
    FROM apartments a
    WHERE a.house_street = s.street_name
);

第二步：实现自动更新 #

要让streets.total_buildings在apartments表新增/修改/删除记录时自动同步更新，不同数据库有不同实现方案：

1. MySQL/MariaDB：使用触发器 #

需要创建三个触发器，分别处理插入、更新、删除操作：

插入触发器

DELIMITER //
CREATE TRIGGER update_total_after_insert
AFTER INSERT ON apartments
FOR EACH ROW
BEGIN
    UPDATE streets s
    SET total_buildings = (
        SELECT COUNT(DISTINCT house_no)
        FROM apartments a
        WHERE a.house_street = s.street_name
    )
    WHERE s.street_name = NEW.house_street;
END //
DELIMITER ;

更新触发器

当修改apartments的house_street或house_no时，同步更新新旧街道的计数：

DELIMITER //
CREATE TRIGGER update_total_after_update
AFTER UPDATE ON apartments
FOR EACH ROW
BEGIN
    -- 更新旧街道的计数
    UPDATE streets s
    SET total_buildings = (
        SELECT COUNT(DISTINCT house_no)
        FROM apartments a
        WHERE a.house_street = s.street_name
    )
    WHERE s.street_name = OLD.house_street;
    
    -- 如果街道发生变更，更新新街道的计数
    IF NEW.house_street != OLD.house_street THEN
        UPDATE streets s
        SET total_buildings = (
            SELECT COUNT(DISTINCT house_no)
            FROM apartments a
            WHERE a.house_street = s.street_name
        )
        WHERE s.street_name = NEW.house_street;
    END IF;
END //
DELIMITER ;

删除触发器

DELIMITER //
CREATE TRIGGER update_total_after_delete
AFTER DELETE ON apartments
FOR EACH ROW
BEGIN
    UPDATE streets s
    SET total_buildings = (
        SELECT COUNT(DISTINCT house_no)
        FROM apartments a
        WHERE a.house_street = s.street_name
    )
    WHERE s.street_name = OLD.house_street;
END //
DELIMITER ;

2. PostgreSQL：使用视图（更轻量） #

如果不需要把total_buildings持久存储在表中，直接创建视图实时计算：

CREATE VIEW streets_with_total AS
SELECT 
    s.street_name,
    (SELECT COUNT(DISTINCT a.house_no) FROM apartments a WHERE a.house_street = s.street_name) AS total_buildings
FROM streets s;

后续查询streets_with_total就能得到实时的楼栋数，无需维护触发器。

3. SQL Server：使用计算列或触发器 #

计算列（实时计算）

ALTER TABLE streets
ADD total_buildings AS (
    SELECT COUNT(DISTINCT house_no) FROM apartments a WHERE a.house_street = streets.street_name
);

计算列会自动实时更新，不需要额外维护。

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