如何在SQL的products表中添加自动计算prod_cat_id频次的生成列

原语句的问题 #

• 语法错误：缺少闭合括号，生成列的正确语法结构应为GENERATED ALWAYS AS (表达式) VIRTUAL
• 逻辑错误：生成列仅支持基于当前行字段的计算，无法使用COUNT()这类跨多行的聚合函数——聚合统计针对的是整个数据集或分组，不属于行级计算范畴。

可行解决方案 #

方案1：使用视图（推荐，无需修改原表结构） #

视图可以实时计算每个分类的出现频次，数据始终保持最新：

CREATE VIEW products_with_count AS
SELECT 
    *,
    (SELECT COUNT(*) FROM products p2 WHERE p2.prod_cat_id = products.prod_cat_id) AS prod_cat_id_count
FROM products;

使用时直接查询该视图即可，例如：SELECT * FROM products_with_count;

方案2：使用触发器维护字段（适合需要将数据存储在表中的场景） #

如果必须在原表中存储这个统计值，可以通过触发器自动更新：

1. 先添加普通字段：

ALTER TABLE products ADD COLUMN prod_cat_id_count INT;

2. 创建触发器函数：

CREATE OR REPLACE FUNCTION update_prod_cat_count()
RETURNS TRIGGER AS $$
BEGIN
    -- 更新当前分类下所有行的计数
    UPDATE products
    SET prod_cat_id_count = (SELECT COUNT(*) FROM products WHERE prod_cat_id = NEW.prod_cat_id)
    WHERE prod_cat_id = NEW.prod_cat_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. 创建插入/更新触发器：

CREATE TRIGGER trigger_update_prod_cat_count
AFTER INSERT OR UPDATE OF prod_cat_id ON products
FOR EACH ROW EXECUTE FUNCTION update_prod_cat_count();

4. 初始化现有数据的计数：

UPDATE products p
SET prod_cat_id_count = (SELECT COUNT(*) FROM products WHERE prod_cat_id = p.prod_cat_id);

方案对比 #

• 视图：无需维护，数据实时准确，但每次查询都会执行聚合计算，适合数据量不大或对实时性要求高的场景。
• 触发器：查询速度更快，但需要维护触发器逻辑，插入/更新数据时会有额外性能开销，适合数据量较大且查询频次远高于更新频次的场景。

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