创建引用其他表的SQL计算列 实现主机游戏数量自动统计

绝大多数主流数据库（MySQL、SQL Server、PostgreSQL等）不支持直接创建跨表关联的原生计算列，因为计算列默认只能引用当前表字段，无法直接关联外部表数据。你可以选择以下两种常用方案实现需求：

方案1：使用视图（推荐，无数据冗余、实时同步） #

不需要修改原表结构，直接创建关联统计视图即可，查询视图就能拿到实时更新的各主机游戏数量：

CREATE OR REPLACE VIEW v_consoles_game_count AS
SELECT 
    c.Console,
    c.Games,
    COUNT(g.GameName) AS GameCount
FROM Consoles c
LEFT JOIN Games g ON c.Console = g.Console
GROUP BY c.Console, c.Games;

方案2：触发器同步持久化字段（需在Consoles表存储GameCount字段时使用） #

如果业务要求必须将GameCount作为Consoles表的实际字段持久化存储，可以通过Games表的增删改触发器自动维护计数：

1. 先给Consoles表新增GameCount字段并初始化历史数据

-- 新增字段
ALTER TABLE Consoles ADD COLUMN GameCount INT DEFAULT 0;
-- 初始化现有计数
UPDATE Consoles c
SET GameCount = (SELECT COUNT(*) FROM Games g WHERE g.Console = c.Console);

2. 创建新增游戏触发逻辑（以MySQL为例）

DELIMITER //
CREATE TRIGGER trg_games_insert
AFTER INSERT ON Games
FOR EACH ROW
BEGIN
    UPDATE Consoles SET GameCount = GameCount + 1 WHERE Console = NEW.Console;
END //
DELIMITER ;

3. 创建删除游戏触发逻辑

DELIMITER //
CREATE TRIGGER trg_games_delete
AFTER DELETE ON Games
FOR EACH ROW
BEGIN
    UPDATE Consoles SET GameCount = GameCount - 1 WHERE Console = OLD.Console;
END //
DELIMITER ;

4. 创建修改游戏所属主机触发逻辑

DELIMITER //
CREATE TRIGGER trg_games_update
AFTER UPDATE ON Games
FOR EACH ROW
BEGIN
    IF OLD.Console <> NEW.Console THEN
        UPDATE Consoles SET GameCount = GameCount - 1 WHERE Console = OLD.Console;
        UPDATE Consoles SET GameCount = GameCount + 1 WHERE Console = NEW.Console;
    END IF;
END //
DELIMITER ;

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