Oracle SQL/PLSQL需求：按指定Flag规则计算Final_value_number

业务需求 #

• 读取表中Flag值为'Y'、'N'、NULL的记录
• 计算Final_value_number的规则：
  • 若同一no_contract下存在Flag为'N'的记录，该合同所有记录的Final_value_number取Flag='N'对应的value_number值
  • 若同一no_contract下不存在Flag为'N'的记录，每条记录的Final_value_number取自身的value_number值

示例数据 #

表格1（存在Flag='N'的情况） #

表格2（无Flag='N'的情况） #

解决方案 #

方法1：使用窗口函数（推荐，无需额外关联） #

通过窗口函数按no_contract分组，直接获取该组内Flag='N'对应的value_number，再通过CASE语句判断赋值：

SELECT
    no_contract,
    no_assesment,
    flag,
    value_number,
    CASE
        WHEN MAX(CASE WHEN flag = 'N' THEN value_number END) OVER (PARTITION BY no_contract) IS NOT NULL
        THEN MAX(CASE WHEN flag = 'N' THEN value_number END) OVER (PARTITION BY no_contract)
        ELSE value_number
    END AS Final_value_number
FROM your_table_name;

逻辑解释：

• MAX(CASE WHEN flag = 'N' THEN value_number END) OVER (PARTITION BY no_contract)：按合同分组，提取该组中Flag='N'的value_number（MAX会自动忽略NULL，无N记录时返回NULL）
• CASE语句：如果分组内存在N记录，则统一使用该值；否则使用当前记录的value_number

方法2：使用子查询+左连接 #

先预查询所有存在N记录的合同对应的value_number，再通过左连接关联原表赋值：

WITH contract_n_values AS (
    -- 提取每个合同下Flag='N'的value_number，若多个N记录可根据业务用MAX/MIN聚合
    SELECT
        no_contract,
        MAX(value_number) AS n_value
    FROM your_table_name
    WHERE flag = 'N'
    GROUP BY no_contract
)
SELECT
    t.no_contract,
    t.no_assesment,
    t.flag,
    t.value_number,
    COALESCE(cnv.n_value, t.value_number) AS Final_value_number
FROM your_table_name t
LEFT JOIN contract_n_values cnv 
    ON t.no_contract = cnv.no_contract;

逻辑解释：

• 子查询contract_n_values：提前筛选出所有有N记录的合同及其对应的value_number（处理多个N记录的情况）
• COALESCE(cnv.n_value, t.value_number)：优先使用N记录的value_number，无则使用当前记录的值

验证结果 #

• 针对表格1：no_contract=1存在N记录，两条记录的Final_value_number均返回200，符合预期
• 针对表格2：no_contract=1无N记录，两条记录分别返回自身的100和200，符合预期

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