如何通过VBA过滤器修改SQL查询中yellow列值并同步更新ROI计算

需求可行性说明 #

该需求完全可实现，核心注意点：ROI依赖yellow字段计算，每次修改yellow取值后必须同步重新计算ROI，才能保证后续筛选的准确性。你提到的「依次应用三个赋值规则」属于分层迭代计算场景，用CTE（公用表表达式）逐层处理即可实现。

具体实现代码 #

WITH 
-- 第一层：引入原始子查询数据
raw_data AS (
    SELECT * FROM [Subqueries]
),
-- 第二层：应用第一个赋值规则，更新yellow
rule1_applied AS (
    SELECT 
        *,
        CASE
            WHEN f_n_s = 'yes' AND roi < 10 THEN ROUND((max1 + max2)/2, 2)
            ELSE yellow -- 不满足条件的行保留原始yellow值，避免生成NULL
        END AS new_yellow
    FROM raw_data
),
-- 第三层：基于第一个规则的结果重新计算ROI，再应用第二个规则
rule2_applied AS (
    SELECT 
        *,
        -- 按固定公式重新计算当前ROI
        ROUND(((col_az*0.7-1.4-6-5-new_yellow) / NULLIF(new_yellow,0)*100),0) AS new_roi,
        CASE
            WHEN f_n_s = 'yes' AND ROUND(((col_az*0.7-1.4-6-5-new_yellow) / NULLIF(new_yellow,0)*100),0) < 10 THEN ROUND((max1 + max2 + max3)/3, 2)
            ELSE new_yellow
        END AS new_yellow2
    FROM rule1_applied
),
-- 第四层：基于第二个规则的结果重新计算ROI，再应用第三个规则
rule3_applied AS (
    SELECT 
        *,
        -- 再次按固定公式重新计算当前ROI
        ROUND(((col_az*0.7-1.4-6-5-new_yellow2) / NULLIF(new_yellow2,0)*100),0) AS final_roi,
        CASE
            WHEN f_n_s = 'yes' AND ROUND(((col_az*0.7-1.4-6-5-new_yellow2) / NULLIF(new_yellow2,0)*100),0) < 10 THEN max5
            ELSE new_yellow2
        END AS final_yellow
    FROM rule2_applied
)
-- 最终输出结果，可按需筛选字段
SELECT *, final_yellow AS yellow, final_roi AS roi
FROM rule3_applied

其他适配场景说明 #

如果三个规则不需要迭代计算，是同一筛选条件下的不同分支（比如满足条件A用规则1、满足条件B用规则2），直接在单层CASE表达式中添加多个WHEN分支即可，示例如下：

SELECT 
    *,
    -- 按分支规则更新yellow
    CASE
        WHEN f_n_s = 'yes' AND roi < 10 AND 【分支条件1】 THEN ROUND((max1+max2)/2,2)
        WHEN f_n_s = 'yes' AND roi < 10 AND 【分支条件2】 THEN ROUND((max1+max2+max3)/3,2)
        WHEN f_n_s = 'yes' AND roi < 10 AND 【分支条件3】 THEN max5
        ELSE yellow
    END as yellow,
    -- 同步更新ROI
    ROUND(((col_az*0.7-1.4-6-5-
        CASE
            WHEN f_n_s = 'yes' AND roi < 10 AND 【分支条件1】 THEN ROUND((max1+max2)/2,2)
            WHEN f_n_s = 'yes' AND roi < 10 AND 【分支条件2】 THEN ROUND((max1+max2+max3)/3,2)
            WHEN f_n_s = 'yes' AND roi < 10 AND 【分支条件3】 THEN max5
            ELSE yellow
        END
    ) / NULLIF(
        CASE
            WHEN f_n_s = 'yes' AND roi < 10 AND 【分支条件1】 THEN ROUND((max1+max2)/2,2)
            WHEN f_n_s = 'yes' AND roi < 10 AND 【分支条件2】 THEN ROUND((max1+max2+max3)/3,2)
            WHEN f_n_s = 'yes' AND roi < 10 AND 【分支条件3】 THEN max5
            ELSE yellow
        END
    ,0)*100),0) as roi
FROM [Subqueries]

注意事项 #

• 你最初的CASE表达式未加ELSE分支，不满足筛选条件的行yellow会被置为NULL，建议根据业务需求补充ELSE逻辑保留原始值
• 重复的ROI计算公式可以封装为自定义函数简化代码，减少冗余

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