如何通过变量简化SQL中重复的时区转换与班次判断代码

注意事项 #

你原本期望的直接用SET @x定义变量的写法无法生效，因为标量变量为单值，无法逐行匹配表中TimeTrue字段的计算结果，我们用CROSS APPLY实现逐行计算结果的复用，效果和你期望的@x完全一致。

优化后完整代码 #

SELECT
    CONVERT(VARCHAR, a.TimeTrue, 23) AS Date,
    a.TimeTrue AS TimeTrueUTC,
    x.TimeTrueCet AS TimeTrue,
    CASE
        WHEN CAST(a.TimeTrue AS DATE) BETWEEN '2021-02-08' AND '2021-07-11' THEN
            CASE
                WHEN x.TimeTrueCetTime BETWEEN '06:00:00' AND '14:59:59' THEN 0
                WHEN x.TimeTrueCetTime BETWEEN '15:00:00' AND '00:54:59' THEN 1
                ELSE 2
            END
        WHEN CAST(a.TimeTrue AS DATE) BETWEEN '2021-07-12' AND '2021-08-29' THEN
            CASE
                WHEN x.TimeTrueCetTime BETWEEN '06:00:00' AND '14:29:59' THEN 0
                WHEN x.TimeTrueCetTime BETWEEN '14:30:00' AND '00:54:59' THEN 1
                ELSE 2
            END
        ELSE
            CASE
                WHEN x.TimeTrueCetTime BETWEEN '06:00:00' AND '13:59:59' THEN 0
                WHEN x.TimeTrueCetTime BETWEEN '14:00:00' AND '23:57:59' THEN 1
                ELSE 2
            END
    END AS ShiftNo
FROM PerformanceOpcArchive a (NOLOCK)
-- 定义可复用的时区转换结果，效果等价于你想要的@x变量
CROSS APPLY (
    SELECT 
        DATEADD(HOUR, CAST(LEFT(RIGHT(CONVERT(DATETIME2(0), a.TimeTrue, 126) AT TIME ZONE 'Central European Standard Time', 4), 1) AS INT), a.TimeTrue) AS TimeTrueCet,
        CAST(DATEADD(HOUR, CAST(LEFT(RIGHT(CONVERT(DATETIME2(0), a.TimeTrue, 126) AT TIME ZONE 'Central European Standard Time', 4), 1) AS INT), a.TimeTrue) AS TIME(0)) AS TimeTrueCetTime
) x
WHERE CONVERT(VARCHAR, a.TimeTrue, 23) BETWEEN '2021-03-24' AND '2021-03-29'
ORDER BY a.TimeTrue ASC;

如果需要进一步简化班次判断逻辑，可以把班次规则独立封装，后续修改规则只需要改一处即可：

-- 定义班次规则，所有规则统一维护，无需修改查询逻辑
DECLARE @ShiftRule TABLE (
    StartDate DATE,
    EndDate DATE,
    Shift0Start TIME,
    Shift0End TIME,
    Shift1Start TIME,
    Shift1End TIME
)
INSERT INTO @ShiftRule VALUES
('2021-02-08', '2021-07-11', '06:00:00', '14:59:59', '15:00:00', '00:54:59'),
('2021-07-12', '2021-08-29', '06:00:00', '14:29:59', '14:30:00', '00:54:59'),
('1900-01-01', '9999-12-31', '06:00:00', '13:59:59', '14:00:00', '23:57:59') -- 默认规则

SELECT
    CONVERT(VARCHAR, a.TimeTrue, 23) AS Date,
    a.TimeTrue AS TimeTrueUTC,
    x.TimeTrueCet AS TimeTrue,
    CASE
        WHEN x.TimeTrueCetTime BETWEEN r.Shift0Start AND r.Shift0End THEN 0
        WHEN x.TimeTrueCetTime BETWEEN r.Shift1Start AND r.Shift1End THEN 1
        ELSE 2
    END AS ShiftNo
FROM PerformanceOpcArchive a (NOLOCK)
CROSS APPLY (
    SELECT 
        DATEADD(HOUR, CAST(LEFT(RIGHT(CONVERT(DATETIME2(0), a.TimeTrue, 126) AT TIME ZONE 'Central European Standard Time', 4), 1) AS INT), a.TimeTrue) AS TimeTrueCet,
        CAST(DATEADD(HOUR, CAST(LEFT(RIGHT(CONVERT(DATETIME2(0), a.TimeTrue, 126) AT TIME ZONE 'Central European Standard Time', 4), 1) AS INT), a.TimeTrue) AS TIME(0)) AS TimeTrueCetTime
) x
-- 自动匹配对应日期的班次规则
OUTER APPLY (
    SELECT TOP 1 * FROM @ShiftRule
    WHERE CAST(a.TimeTrue AS DATE) BETWEEN StartDate AND EndDate
    ORDER BY StartDate DESC
) r
WHERE CONVERT(VARCHAR, a.TimeTrue, 23) BETWEEN '2021-03-24' AND '2021-03-29'
ORDER BY a.TimeTrue ASC;

优化说明 #

• 用CROSS APPLY把重复的时区转换逻辑封装成可复用的别名，后续查询直接引用即可，无需重复写转换代码，完全满足你想要的类似@x的调用效果
• 进阶优化方案将班次规则独立成表变量，所有规则统一维护，后续调整班次时间、新增规则都不需要修改查询逻辑，彻底消除重复代码

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