如何在CASE WHEN中获取最大日期且无需依赖GETDATE函数？

样本数据 #

需求 #

获取最近两天的记录，新增三列：

• sum_act：当日debet的总和
• sum_prev：前一日debet的总和
• diff：sum_act与sum_prev的差值

尝试的SQL及问题 #

最初编写的SQL因在CASE WHEN中直接嵌套聚合函数MAX(date)报错：

SELECT
    [debet],
    [date] , 
    SUM( CASE WHEN [date] = MAX(date)     THEN [debet] ELSE 0 END ) AS sum_act, 
    SUM( CASE WHEN [date] = MAX(date) - 1 THEN [debet] ELSE 0 END ) AS sum_prev , 
    (
        SUM( CASE WHEN [date] = MAX(date)     THEN [debet] ELSE 0 END ) 
        -
        SUM( CASE WHEN [date] = MAX(date) - 1 THEN [debet] ELSE 0 END )  
    ) AS diff 
FROM
    Table 
WHERE
    [date] = ( SELECT MAX(date) FROM Table WHERE date < ( SELECT MAX(date) FROM Table) )
    OR
    [date] = ( SELECT MAX(date) FROM Table WHERE date = ( SELECT MAX(date) FROM Table ) )
GROUP BY
    [date],
    [debet]

错误原因：聚合函数（如MAX()）不能嵌套在另一个聚合函数（如SUM()）的CASE WHEN条件中。

目前临时写法需要手动调整日期参数适配周末/节假日，灵活性差：

sum(CASE WHEN [date] = dateadd(dd,-3,cast(getdate() as date)) THEN [debet] ELSE 0 END)

希望找到无需依赖GETDATE()、自动获取最大日期的解决方案。

预期结果 #

解决方案 #

通过预计算最近两天的日期，避免聚合函数嵌套，以下是两种可行写法：

方法1：使用CTE预定义日期范围 #

WITH DateParams AS (
    SELECT
        MAX(date) AS current_date,
        MAX(date) - 1 AS previous_date
    FROM [Table]
),
DailySums AS (
    SELECT
        date,
        SUM(debet) AS daily_total
    FROM [Table]
    WHERE date IN (SELECT current_date FROM DateParams)
       OR date IN (SELECT previous_date FROM DateParams)
    GROUP BY date
)
SELECT
    ds.date,
    CASE WHEN ds.date = dp.current_date THEN ds.daily_total ELSE 0 END AS sum_act,
    CASE WHEN ds.date = dp.previous_date THEN ds.daily_total ELSE 0 END AS sum_prev,
    CASE WHEN ds.date = dp.current_date THEN ds.daily_total ELSE -ds.daily_total END AS diff
FROM DailySums ds
CROSS JOIN DateParams dp

方法2：子查询直接引用日期参数 #

SELECT
    ds.date,
    CASE WHEN ds.date = (SELECT MAX(date) FROM [Table]) THEN ds.daily_total ELSE 0 END AS sum_act,
    CASE WHEN ds.date = (SELECT MAX(date) FROM [Table] WHERE date < (SELECT MAX(date) FROM [Table])) THEN ds.daily_total ELSE 0 END AS sum_prev,
    CASE 
        WHEN ds.date = (SELECT MAX(date) FROM [Table]) THEN ds.daily_total 
        ELSE -ds.daily_total 
    END AS diff
FROM (
    SELECT
        date,
        SUM(debet) AS daily_total
    FROM [Table]
    WHERE date IN (
        SELECT MAX(date) FROM [Table],
        SELECT MAX(date) FROM [Table] WHERE date < (SELECT MAX(date) FROM [Table])
    )
    GROUP BY date
) ds

说明 #

1. 先计算每日debet总和，避免重复计算；
2. 预获取最近两天的日期（最大日期及前一天），作为CASE WHEN的判断条件；
3. 按日期分组输出，完全匹配预期结果格式。

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