SQL新手求助：如何创建包含基于其他列计算值的表？

针对你的需求，下面提供几种不同场景下的实现方案，根据你使用的数据库类型选择即可：

1. 带生成列的表创建（推荐，支持的数据库：MySQL 5.7+/SQL Server 2016+/PostgreSQL 12+） #

这种方案会让数据库自动维护计算字段的值，无需手动处理，当Ingredient表的Price更新时，MenuItem的计算字段会自动同步（取决于生成列类型）。

MySQL 示例代码 #

CREATE TABLE MenuItem (
    FoodID INT PRIMARY KEY AUTO_INCREMENT, -- 自增主键，可根据需求调整字段类型
    FoodName VARCHAR(100) NOT NULL,
    IngredientID INT NOT NULL,
    -- 生产成本：食材价格的2倍，关联Ingredient表计算
    ProductionCost DECIMAL(10,2) GENERATED ALWAYS AS (
        (SELECT Price * 2 FROM Ingredient i WHERE i.IngredientID = MenuItem.IngredientID)
    ) STORED, -- STORED表示值存储在表中，VIRTUAL表示仅查询时计算
    -- 售价：生产成本的2倍
    SaleCost DECIMAL(10,2) GENERATED ALWAYS AS (ProductionCost * 2) STORED,
    -- 外键约束，关联Ingredient表的主键
    FOREIGN KEY (IngredientID) REFERENCES Ingredient(IngredientID)
        ON DELETE RESTRICT ON UPDATE CASCADE -- 可根据业务需求调整约束规则
);

SQL Server 示例代码 #

CREATE TABLE MenuItem (
    FoodID INT PRIMARY KEY IDENTITY(1,1),
    FoodName VARCHAR(100) NOT NULL,
    IngredientID INT NOT NULL,
    ProductionCost AS (
        (SELECT Price * 2 FROM Ingredient i WHERE i.IngredientID = MenuItem.IngredientID)
    ),
    SaleCost AS (ProductionCost * 2),
    FOREIGN KEY (IngredientID) REFERENCES Ingredient(IngredientID)
);

2. 数据库不支持生成列的替代方案 #

方案A：插入数据时手动计算 #

先创建仅包含基础字段的表，插入数据时通过子查询获取食材价格并计算：

-- 创建表结构
CREATE TABLE MenuItem (
    FoodID INT PRIMARY KEY AUTO_INCREMENT,
    FoodName VARCHAR(100) NOT NULL,
    IngredientID INT NOT NULL,
    ProductionCost DECIMAL(10,2) NOT NULL,
    SaleCost DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (IngredientID) REFERENCES Ingredient(IngredientID)
);

-- 插入示例数据
INSERT INTO MenuItem (FoodName, IngredientID, ProductionCost, SaleCost)
VALUES (
    '番茄炒蛋',
    1, -- 对应Ingredient表中IngredientID=1的食材
    (SELECT Price * 2 FROM Ingredient WHERE IngredientID=1),
    (SELECT Price * 4 FROM Ingredient WHERE IngredientID=1)
);

方案B：创建视图动态计算 #

如果不需要存储计算值，希望每次查询都获取最新的价格计算结果，可以创建视图：

CREATE VIEW MenuItemView AS
SELECT
    m.FoodID,
    m.FoodName,
    m.IngredientID,
    i.Price * 2 AS ProductionCost,
    i.Price * 4 AS SaleCost -- 等价于生产成本的2倍
FROM MenuItem m
JOIN Ingredient i ON m.IngredientID = i.IngredientID;

查询该视图即可得到实时计算的生产成本和售价，当Ingredient表的Price更新时，视图结果会自动同步。

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