如何在SQL中实现依赖其他行值的自动失效评分列？

需求说明 #

• 数据表包含字段：Employee（员工姓名）、Category（评分类别）、Score（原始评分）
• 规则：若某员工在Bar类别中的得分为0，则该员工所有类别的Calculated Score均置为0；否则保留原Score值

SQL实现方案 #

方法1：窗口函数实现（推荐，高效简洁） #

利用窗口函数按员工分组，判断该员工是否存在Bar类别得0的记录，再输出计算后的分数：

SELECT
    Employee,
    Category,
    Score,
    CASE
        -- 检查当前员工是否有Bar类别得0的记录
        WHEN MAX(CASE WHEN Category = 'Bar' AND Score = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY Employee) = 1
        THEN 0
        ELSE Score
    END AS Calculated_Score
FROM
    employee_evaluations;

逻辑解释：

• PARTITION BY Employee：按员工分组，针对每个员工单独做判断
• 内层CASE：将员工的Bar-0记录标记为1，其他记录标记为0
• MAX(...) OVER (...)：若员工存在Bar-0记录，分组后的最大值为1，否则为0
• 外层CASE：根据标记结果，触发自动失效则置0，否则保留原分数

方法2：子查询关联实现（兼容旧版数据库） #

如果数据库不支持窗口函数，可以通过子查询筛选出触发自动失效的员工列表，再关联原表判断：

SELECT
    e.Employee,
    e.Category,
    e.Score,
    CASE
        WHEN af.Employee IS NOT NULL THEN 0
        ELSE e.Score
    END AS Calculated_Score
FROM
    employee_evaluations e
LEFT JOIN (
    -- 筛选所有Bar类别得0的员工
    SELECT DISTINCT Employee
    FROM employee_evaluations
    WHERE Category = 'Bar' AND Score = 0
) af ON e.Employee = af.Employee;

逻辑解释：

• 子查询af：生成触发自动失效的员工名单
• 左关联原表：若员工在失效名单中，所有分数置0，否则保留原分数

效果验证 #

假设原始数据如下：

执行SQL后得到的结果：

完全符合需求：Randy和Joe的所有评分均置为0，其他员工保留原分数。

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