如何基于单一行的条件为同类别所有行批量赋值？

问题背景 #

现有表结构及需求如下：

需求：生成Calculation列，当某Category下存在Sub Category为Table时，该分类下所有行的Calculation值为1，否则为0。

原查询语句功能正常，但耗时从1分钟飙升至50分钟甚至超时：

CASE WHEN category IN (SELECT category FROM table WHERE sub-category='Table') THEN 1 ELSE 0 END Calculation

高效写法推荐 #

1. 窗口函数（适配MySQL 8.0+、PostgreSQL、SQL Server等） #

利用窗口函数在单次扫描内完成分组判断，避免子查询的重复执行：

SELECT 
    category,
    sub_category,
    MAX(CASE WHEN sub_category = 'Table' THEN 1 ELSE 0 END) OVER (PARTITION BY category) AS Calculation
FROM your_table_name;

按category分组后，直接计算每组是否存在目标子类别，性能远优于原写法。

2. 预聚合+关联（兼容低版本数据库） #

先一次性筛选出包含Table的分类，再通过关联匹配原表：

SELECT 
    t.category,
    t.sub_category,
    CASE WHEN c.category IS NOT NULL THEN 1 ELSE 0 END AS Calculation
FROM your_table_name t
LEFT JOIN (
    SELECT DISTINCT category 
    FROM your_table_name 
    WHERE sub_category = 'Table'
) c ON t.category = c.category;

子查询仅执行一次生成小结果集，再与原表关联，避免IN子查询的逐行匹配开销。

额外性能优化建议 #

• 给sub_category和category建立联合索引：CREATE INDEX idx_subcat_cat ON your_table_name(sub_category, category);，可大幅加速分类判断逻辑。
• 大表场景下尽量避免使用IN子查询，部分数据库对其会做逐行校验，导致性能急剧下降。

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