SQL Server Express中利用ROLLUP合并早6点前跨日期数据的视图改造

核心思路是先调整日期（将早于6点的记录归属到前一天），再进行ROLLUP汇总，而非直接在ROLLUP逻辑里处理日期修正。具体实现如下：

完整SQL代码 #

WITH adjusted_data AS (
    SELECT
        MACH,
        -- 时间早于06:00则日期减1天，否则保留原日期
        CASE 
            WHEN TIME < '06:00:00' THEN DATEADD(DAY, -1, DATE)
            ELSE DATE
        END AS adjusted_date,
        VAL
    FROM myTable
)
SELECT        
    COALESCE(MACH, 'Total') AS MACH,
    COALESCE(CONVERT(VARCHAR, adjusted_date, 103), '') AS 'DATE', -- 保持DD/MM/YYYY格式输出
    SUM(VAL) AS VAL
FROM adjusted_data
GROUP BY 
    ROLLUP (MACH, adjusted_date)
HAVING
    (MACH IS NULL) OR (adjusted_date IS NOT NULL)

代码说明 #

1. 日期修正逻辑：通过CTE（公共表表达式）adjusted_data预处理数据，对每条记录的日期做判断——若TIME早于06:00:00，就用DATEADD将日期减1天，确保这类记录归属到前一天的统计中。
2. 基于修正后的数据汇总：主查询使用修正后的adjusted_date替代原DATE字段，执行ROLLUP分组汇总，保证统计逻辑基于调整后的日期维度。
3. 格式与过滤适配：用CONVERT(VARCHAR, adjusted_date, 103)将日期转换为你原视图使用的DD/MM/YYYY格式；同时调整HAVING条件，过滤掉机器分组下的空日期汇总行，只保留最终的全局总计行。

验证结果 #

代入你提供的测试数据运行上述代码，将得到期望的输出：

| MACH  | DATE       | VAL |
| 1     | 02/05/2024 | 300 |
| 1     | 03/05/2024 | 100 |
| 2     | 02/05/2024 | 200 |
| Total |            | 600 |

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