SQL中CASE函数能否嵌套子查询？将IF逻辑改写为CASE实现

需求说明 #

现有一段用IF语句编写的SQL代码，需要将IF分支逻辑改为CASE函数实现相同功能，同时想了解CASE函数内是否可以使用子查询。WEEKNUMBER表仅存储1至53的数字，代码逻辑是根据指定日期的星期数计算每周的起止日期，原代码如下：

declare @currYear int = datepart(year,getdate());
declare @date date = datefromparts(@currYear,1,1);
--declare @date date = '2022-01-01'

declare @firstDayOfWeekToLast   date = DATEADD(day,-datepart(weekday,@date) + 2,@date)
declare @lastDayOfWeekToLast    date = DATEADD(day,-datepart(weekday,@date) + 6,@date)

declare @firstDayOfWeekToNew    date = DATEADD(day,-datepart(weekday,@date) - 5,@date)
declare @lastDayOfWeekToNew     date = DATEADD(day,-datepart(weekday,@date) - 1,@date)


select DATEPART(WEEKDAY,@date)

if(DATEPART(weekday,@date) > 5)
    begin
        select  format(DATEADD(week,WeekNumber,@firstDayOfWeekToLast),'dd.MM.yyyy') as 'First day of Week',
                format(DATEADD(week,WeekNumber,@lastDayOfWeekToLast),'dd.MM.yyyy') as 'Last day of Week'
        from WEEKNUMBER
    end

else if(DATEPART(weekday,@date) = 1)
    begin
    select  format(DATEADD(week,WeekNumber-1,@firstDayOfWeekToLast),'dd.MM.yyyy') as 'First day of Week',
                format(DATEADD(week,WeekNumber-1,@lastDayOfWeekToLast),'dd.MM.yyyy') as 'Last day of Week'
        from WEEKNUMBER
    end

else
    begin
        select  format(DATEADD(week,WeekNumber,@firstDayOfWeekToNew),'dd.MM.yyyy') as 'First day of Week',
                format(DATEADD(week,WeekNumber,@lastDayOfWeekToNew),'dd.MM.yyyy') as 'Last day of Week'
        from WEEKNUMBER
    end

改写后的CASE版本代码 #

可以将原有的IF分支逻辑整合到SELECT语句中，通过CASE函数动态选择对应的基准日期和偏移量，实现相同的计算逻辑：

declare @currYear int = datepart(year,getdate());
declare @date date = datefromparts(@currYear,1,1);
--declare @date date = '2022-01-01'

declare @firstDayOfWeekToLast   date = DATEADD(day,-datepart(weekday,@date) + 2,@date)
declare @lastDayOfWeekToLast    date = DATEADD(day,-datepart(weekday,@date) + 6,@date)

declare @firstDayOfWeekToNew    date = DATEADD(day,-datepart(weekday,@date) - 5,@date)
declare @lastDayOfWeekToNew     date = DATEADD(day,-datepart(weekday,@date) - 1,@date)

select DATEPART(WEEKDAY,@date)

-- 使用CASE函数替代IF分支
select 
    format(
        DATEADD(week, 
            case 
                when DATEPART(weekday,@date) = 1 then WeekNumber - 1
                else WeekNumber
            end,
            case 
                when DATEPART(weekday,@date) > 5 then @firstDayOfWeekToLast
                when DATEPART(weekday,@date) = 1 then @firstDayOfWeekToLast
                else @firstDayOfWeekToNew
            end
        ),'dd.MM.yyyy') as 'First day of Week',
    format(
        DATEADD(week, 
            case 
                when DATEPART(weekday,@date) = 1 then WeekNumber - 1
                else WeekNumber
            end,
            case 
                when DATEPART(weekday,@date) > 5 then @lastDayOfWeekToLast
                when DATEPART(weekday,@date) = 1 then @lastDayOfWeekToLast
                else @lastDayOfWeekToNew
            end
        ),'dd.MM.yyyy') as 'Last day of Week'
from WEEKNUMBER

CASE函数内使用子查询的说明 #

CASE函数内完全可以使用子查询，只要子查询返回的是单一值（单行单列）即可。例如：

select 
    case 
        when WeekNumber > (select avg(WeekNumber) from WEEKNUMBER) then '大于平均周数'
        else '小于等于平均周数'
    end as WeekCategory
from WEEKNUMBER

上述示例中，CASE的WHEN条件里使用了子查询获取WEEKNUMBER表的平均周数，以此判断每条记录的类别。

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