如何在SELECT查询中复用CASE表达式计算结果优化性能

你可以通过嵌套子查询或**CTE（公共表表达式）**先计算出所有需要复用的中间字段（行号、日期偏移量、核销月份），再在外层查询中直接引用这些结果拼接Write-off-Key。这种方式既避免了重复执行CASE逻辑，也不会因为ConsLookUpID重复导致交叉连接问题，同时还能简化代码结构。

最优实现：嵌套子查询（彻底避免重复计算） #

SELECT
    [Write-off-Count],
    [Write-off-Month],
    [RevLookUpId] + '_' + [Write-off-Month] AS [Write-off-Key]
FROM (
    SELECT
        [RevLookUpId],
        rn AS [Write-off-Count],
        -- 基于已计算的行号和日期偏移量生成核销月份
        CASE
            WHEN rn <= 3 THEN
                FORMAT(
                    DATEADD(MONTH, -(rn - 1 + date_offset), GETDATE()),
                    'yyyy-MM'
                )
            ELSE ''
        END AS [Write-off-Month]
    FROM (
        -- 最内层：只计算一次行号和日期偏移量
        SELECT
            [RevLookUpId],
            ROW_NUMBER() OVER(PARTITION BY [ConsLookUpID] ORDER BY [ConsLookUpID]) AS rn,
            CASE WHEN DATEPART(DAY, GETDATE()) > 15 THEN 0 ELSE 1 END AS date_offset
        FROM ABC
    ) AS InnerCalc
) AS OuterCalc;

替代方案：CTE写法（可读性更强） #

如果更偏好代码的可读性，也可以用CTE拆分计算步骤：

WITH Step1_CalcBase AS (
    -- 第一步：计算行号和日期偏移基准值
    SELECT
        [RevLookUpId],
        ROW_NUMBER() OVER(PARTITION BY [ConsLookUpID] ORDER BY [ConsLookUpID]) AS rn,
        CASE WHEN DATEPART(DAY, GETDATE()) > 15 THEN 0 ELSE 1 END AS date_offset
    FROM ABC
),
Step2_CalcMonth AS (
    -- 第二步：基于基准值计算核销月份
    SELECT
        [RevLookUpId],
        rn AS [Write-off-Count],
        CASE
            WHEN rn <= 3 THEN
                FORMAT(DATEADD(MONTH, -(rn - 1 + date_offset), GETDATE()), 'yyyy-MM')
            ELSE ''
        END AS [Write-off-Month]
    FROM Step1_CalcBase
)
-- 第三步：拼接最终的核销Key
SELECT
    [Write-off-Count],
    [Write-off-Month],
    [RevLookUpId] + '_' + [Write-off-Month] AS [Write-off-Key]
FROM Step2_CalcMonth;

方案优势 #

1. 消除重复计算：行号（ROW_NUMBER）和日期偏移量只计算一次，后续逻辑直接引用结果，大幅降低数据库计算开销。
2. 避免连接问题：全程基于原表逐行处理，不需要通过ConsLookUpID做表连接，彻底解决交叉连接的问题。
3. 简化逻辑结构：把原有的多层嵌套CASE拆分，通过基准偏移量统一处理月份计算，代码更简洁易维护。

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