如何用子查询简化视图中重复的CASE语句逻辑？

你可以通过以下几种方式提取重复的CASE逻辑，避免多次编写相同代码：

方法1：使用子查询（Derived Table） #

把重复的CASE逻辑放到子查询里，在外层直接引用计算好的字段，同时EventsRemaining可以用MAX函数替代嵌套CASE，更简洁：

SELECT
    -- 直接引用子查询里的计算结果，用MAX处理小于0的情况
    MAX(ExpectedNumofEvents - ISNULL(COM.OutreachCount + COM.FVMsgCount, 0), 0) AS EventsRemaining,
    ExpectedNumofEvents
FROM (
    SELECT
        VEH.DriverOutreachProfileId,
        VEH.FVMessagingInd,
        CPP.ExpectedNumberofEvents,
        COM.OutreachCount,
        COM.FVMsgCount,
        -- 只写一次CASE逻辑
        CASE
            WHEN ISNULL(VEH.DriverOutreachProfileId, 0) > 0 AND VEH.FVMessagingInd = 1
                THEN ISNULL(CPP.ExpectedNumberofEvents, 0) + 1
            WHEN ISNULL(VEH.DriverOutreachProfileId, 0) > 0
                THEN ISNULL(CPP.ExpectedNumberofEvents, 0)
            WHEN VEH.FVMessagingInd = 1
                THEN 1
            ELSE 0
        END AS ExpectedNumofEvents
    -- 替换为你原View里的FROM和JOIN语句
    FROM YourTable VEH
    LEFT JOIN CPP ON ... -- 原关联条件
    LEFT JOIN COM ON ... -- 原关联条件
) AS SubQuery

方法2：使用CTE（公共表表达式） #

和子查询逻辑类似，结构更清晰，适合后续需要多次引用中间结果的场景：

WITH CTE_Calculations AS (
    SELECT
        VEH.DriverOutreachProfileId,
        VEH.FVMessagingInd,
        CPP.ExpectedNumberofEvents,
        COM.OutreachCount,
        COM.FVMsgCount,
        -- 一次性计算重复的CASE逻辑
        CASE
            WHEN ISNULL(VEH.DriverOutreachProfileId, 0) > 0 AND VEH.FVMessagingInd = 1
                THEN ISNULL(CPP.ExpectedNumberofEvents, 0) + 1
            WHEN ISNULL(VEH.DriverOutreachProfileId, 0) > 0
                THEN ISNULL(CPP.ExpectedNumberofEvents, 0)
            WHEN VEH.FVMessagingInd = 1
                THEN 1
            ELSE 0
        END AS ExpectedNumofEvents
    -- 原View的FROM和JOIN
    FROM YourTable VEH
    LEFT JOIN CPP ON ...
    LEFT JOIN COM ON ...
)
SELECT
    MAX(ExpectedNumofEvents - ISNULL(OutreachCount + FVMsgCount, 0), 0) AS EventsRemaining,
    ExpectedNumofEvents
FROM CTE_Calculations

方法3：使用CROSS APPLY（适用于SQL Server/Azure SQL等） #

这种方式可以在主查询中直接计算一次重复逻辑，不需要嵌套子查询或CTE，写法更灵活：

SELECT
    MAX(Calc.ExpectedNumofEvents - ISNULL(COM.OutreachCount + COM.FVMsgCount, 0), 0) AS EventsRemaining,
    Calc.ExpectedNumofEvents
FROM YourTable VEH
LEFT JOIN CPP ON ... -- 原关联条件
LEFT JOIN COM ON ... -- 原关联条件
CROSS APPLY (
    SELECT
        CASE
            WHEN ISNULL(VEH.DriverOutreachProfileId, 0) > 0 AND VEH.FVMessagingInd = 1
                THEN ISNULL(CPP.ExpectedNumberofEvents, 0) + 1
            WHEN ISNULL(VEH.DriverOutreachProfileId, 0) > 0
                THEN ISNULL(CPP.ExpectedNumberofEvents, 0)
            WHEN VEH.FVMessagingInd = 1
                THEN 1
            ELSE 0
        END AS ExpectedNumofEvents
) AS Calc

补充说明 #

原代码中EventsRemaining的嵌套CASE可以直接用MAX(计算值, 0)替代，因为当计算结果小于0时返回0，否则返回计算值，逻辑完全一致，代码更简洁。

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