SQL嵌套IIf仅支持10级时多流程映射字段创建问题咨询

问题根因 #

你使用的T-SQL语法中，嵌套IIF函数本质是嵌套CASE表达式，SQL Server对这类嵌套表达式的深度限制为最高10层，超过20个映射规则必然触发Case expressions may only be nested to level 10报错。

解决方案 #

这里提供两种无嵌套限制的改造方案，都可以支持超过20个映射规则： #

方案1：平级CASE WHEN改造

将嵌套IIF直接替换为平级的CASE WHEN结构，无需嵌套，支持任意数量的分支判断，改造后仅需修改原CROSS APPLY生成Missions的逻辑即可：

select Missions, Sum(Morning) Morning, Sum(PM) PM, Sum(Night) Night, count(*) Total
from [dbo].[VIEW_JOBS_FINISHED_ALL]
cross apply (values (
    CASE
        WHEN QUELLE in ('Réception_14','Réception_21') THEN 'M1'
        WHEN QUELLE in ('Réception_17','Réception_16') THEN 'M2'
        WHEN QUELLE in ('Réception_13','Réception_19') THEN 'M3'
        WHEN QUELLE in ('Réception_15','Réception_25') THEN 'M4'
        -- 此处按你的实际规则继续添加更多WHEN分支即可，无数量限制
        WHEN QUELLE in ('Réception_15','Réception_25') THEN 'M5'
        WHEN QUELLE in ('Réception_15','Réception_25') THEN 'M6'
        WHEN QUELLE in ('Réception_15','Réception_25') THEN 'M7'
        WHEN QUELLE in ('Réception_15','Réception_25') THEN 'M8'
        WHEN QUELLE in ('Réception_15','Réception_25') THEN 'M9'
        WHEN QUELLE in ('Réception_15','Réception_25') THEN 'M10'
        WHEN QUELLE in ('Réception_15','Réception_25') THEN 'M11'
        ELSE 'M28'
    END
))f(Missions)
cross apply (values ( [START_DATE] ))v(T)
cross apply
(   
    values (convert(datetime, convert(date, getdate())),
            convert(datetime, convert(date, getdate() - 1)))
) dates (today, yesterday)
cross apply
(
    values (dateadd(hour, 6, yesterday), 
            dateadd(hour, 14, yesterday), 
            dateadd(hour, 21, yesterday), 
            dateadd(hour, 6, today))
) dt (y6, y11, y22, t6)
cross apply (
    select
        case when T >= y6 and T < y11 then 1 else 0 end Morning,
        case when T >=y11 and T < y22 then 1 else 0 end PM,
        case when T >=y22 and T < t6 then 1 else 0 end Night
)c
group by Missions

方案2：虚拟映射表匹配（更推荐）

如果后续映射规则还会新增，推荐把所有映射规则单独抽成虚拟表，后续加规则仅需新增映射行即可，无需修改判断逻辑：

select final_missions as Missions, Sum(Morning) Morning, Sum(PM) PM, Sum(Night) Night, count(*) Total
from [dbo].[VIEW_JOBS_FINISHED_ALL]
-- 所有映射规则统一定义在此处，支持任意数量新增
left join (values
    ('Réception_14','M1'),
    ('Réception_21','M1'),
    ('Réception_17','M2'),
    ('Réception_16','M2'),
    ('Réception_13','M3'),
    ('Réception_19','M3'),
    ('Réception_15','M4'),
    ('Réception_25','M4')
    -- 继续添加其余的QUELLE到Missions的映射即可
) map(QUELLE_VAL, Missions) on QUELLE = map.QUELLE_VAL
-- 未匹配到规则的默认赋值为M28
cross apply (values (ISNULL(Missions, 'M28'))) f(final_missions)
cross apply (values ( [START_DATE] ))v(T)
cross apply
(   
    values (convert(datetime, convert(date, getdate())),
            convert(datetime, convert(date, getdate() - 1)))
) dates (today, yesterday)
cross apply
(
    values (dateadd(hour, 6, yesterday), 
            dateadd(hour, 14, yesterday), 
            dateadd(hour, 21, yesterday), 
            dateadd(hour, 6, today))
) dt (y6, y11, y22, t6)
cross apply (
    select
        case when T >= y6 and T < y11 then 1 else 0 end Morning,
        case when T >=y11 and T < y22 then 1 else 0 end PM,
        case when T >=y22 and T < t6 then 1 else 0 end Night
)c
group by final_missions

注意：你原代码中M4到M11的QUELLE判断条件均重复写为('Réception_15','Réception_25')，替换为你实际的业务规则即可。

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