如何在SQL查询的条件中使用带指定时间的昨日日期

该方案适配SQL Server数据库，完全匹配你提供的原SQL语法特征：

核心逻辑修正说明 #

• 原SQL将START_DATE转换为time类型仅保留时间部分，无法完成「昨日指定时段」的日期+时间联合判断，需要直接使用完整的START_DATE datetime值做判断
• 原夜间时段逻辑存在错误：昨日22点之后的时段需统计到今日6点，而非昨日6点

方案1：使用变量定义时段（可读性更高） #

-- 定义昨日各时段节点
DECLARE @Yesterday DATE = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
DECLARE @Yesterday6AM DATETIME = DATEADD(HOUR, 6, CAST(@Yesterday AS DATETIME))
DECLARE @Yesterday11AM DATETIME = DATEADD(HOUR, 11, CAST(@Yesterday AS DATETIME))
DECLARE @Yesterday10PM DATETIME = DATEADD(HOUR, 22, CAST(@Yesterday AS DATETIME))
DECLARE @Today6AM DATETIME = DATEADD(HOUR, 6, CAST(CAST(GETDATE() AS DATE) AS DATETIME))

select Flow, Sum(Morning) Morning, Sum(PM) PM, Sum(Night) Night, Count(*) Total
from [dbo].[MISSION]
-- 提前过滤仅保留目标时段数据，大幅提升查询性能
WHERE START_DATE >= @Yesterday6AM AND START_DATE < @Today6AM
cross apply (values (Iif(QUELLE in ('Réception_14','Réception_21'),'Flow 1',
                        Iif(QUELLE in ('Réception_17','Réception_16'),'Flow 2','Flow3'))))f(Flow)
cross apply (
    select
        case when START_DATE >= @Yesterday6AM and START_DATE < @Yesterday11AM then 1 else 0 end Morning,
        case when START_DATE >= @Yesterday11AM and START_DATE < @Yesterday10PM then 1 else 0 end PM,
        case when START_DATE >= @Yesterday10PM and START_DATE < @Today6AM then 1 else 0 end Night
)c
group by Flow

方案2：直接内嵌表达式（无需提前声明变量） #

select Flow, Sum(Morning) Morning, Sum(PM) PM, Sum(Night) Night, Count(*) Total
from [dbo].[MISSION]
-- 提前过滤数据范围提升性能
WHERE START_DATE >= DATEADD(HOUR, 6, CAST(DATEADD(DAY, -1, CAST(GETDATE() AS DATE)) AS DATETIME))
  AND START_DATE < DATEADD(HOUR, 6, CAST(CAST(GETDATE() AS DATE) AS DATETIME))
cross apply (values (Iif(QUELLE in ('Réception_14','Réception_21'),'Flow 1',
                        Iif(QUELLE in ('Réception_17','Réception_16'),'Flow 2','Flow3'))))f(Flow)
cross apply (
    select
        case when START_DATE >= DATEADD(HOUR, 6, CAST(DATEADD(DAY, -1, CAST(GETDATE() AS DATE)) AS DATETIME)) 
                    and START_DATE < DATEADD(HOUR, 11, CAST(DATEADD(DAY, -1, CAST(GETDATE() AS DATE)) AS DATETIME)) then 1 else 0 end Morning,
        case when START_DATE >= DATEADD(HOUR, 11, CAST(DATEADD(DAY, -1, CAST(GETDATE() AS DATE)) AS DATETIME)) 
                    and START_DATE < DATEADD(HOUR, 22, CAST(DATEADD(DAY, -1, CAST(GETDATE() AS DATE)) AS DATETIME)) then 1 else 0 end PM,
        case when START_DATE >= DATEADD(HOUR, 22, CAST(DATEADD(DAY, -1, CAST(GETDATE() AS DATE)) AS DATETIME)) 
                    and START_DATE < DATEADD(HOUR, 6, CAST(CAST(GETDATE() AS DATE) AS DATETIME)) then 1 else 0 end Night
)c
group by Flow

注意事项 #

• 上述方案使用GETDATE()取服务器本地时间，若需使用UTC时间替换为GETUTCDATE()即可
• 新增的WHERE条件提前过滤了目标时段外的数据，可大幅提升查询性能

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