sql - 用 COALESCE 重写 CASE WHEN 语句后出错

问题描述

在我的代码中，我有一个CASE WHEN包含 25when秒的语句，现在我需要在链接服务器中使用此代码，所以我被迫用更改CASE WHEN语句COALESCE，但我认为我没有做对。

你能帮我看看，问题出在哪里？

前：

Case MRE100.RECTYQ
    When 'T' then
      Case MGC100.GNTIND
        when 01 then MSK500.S5MV01
        when 02 then MSK500.S5MV02
        when 03 then MSK500.S5MV03
        when 04 then MSK500.S5MV04
        when 05 then MSK500.S5MV05
        when 06 then MSK500.S5MV06
        when 07 then MSK500.S5MV07
        when 08 then MSK500.S5MV08
        when 09 then MSK500.S5MV09
        when 10 then MSK500.S5MV10
        when 11 then MSK500.S5MV11
        when 12 then MSK500.S5MV12
        when 13 then MSK500.S5MV13
        when 14 then MSK500.S5MV14
        when 15 then MSK500.S5MV15
        when 16 then MSK500.S5MV16
        when 17 then MSK500.S5MV17
        when 18 then MSK500.S5MV18
        when 19 then MSK500.S5MV19
        when 20 then MSK500.S5MV20
        when 21 then MSK500.S5MV21
        when 22 then MSK500.S5MV22
        when 23 then MSK500.S5MV23
        when 24 then MSK500.S5MV24
        when 25 then MSK500.S5MV25              
        else 0
      end
    when 'U' then MSK500.S5MVTT
    else 0
end

这会产生以下错误： Msg 125, Level 15, State 4, Line 1 Case expressions may only be nested to level 10

我用 COALESCE 重写：

COALESCE(
    Case when MRE100.RECTYQ = 'T' then MGC100.GNTIND 
         when MRE100.RECTYQ = '01' then MSK500.S5MV01
         when MRE100.RECTYQ = '02' then MSK500.S5MV02
         when MRE100.RECTYQ = '03' then MSK500.S5MV03
         when MRE100.RECTYQ = '04' then MSK500.S5MV04
         when MRE100.RECTYQ = '05' then MSK500.S5MV05
         when MRE100.RECTYQ = '06' then MSK500.S5MV06
         when MRE100.RECTYQ = '07' then MSK500.S5MV07
         when MRE100.RECTYQ = '08' then MSK500.S5MV08
         ELSE NULL
    END,
    Case when MRE100.RECTYQ = '09' then MSK500.S5MV09
         when MRE100.RECTYQ = '10' then MSK500.S5MV10
         when MRE100.RECTYQ = '11' then MSK500.S5MV11
         when MRE100.RECTYQ = '12' then MSK500.S5MV12
         when MRE100.RECTYQ = '13' then MSK500.S5MV13
         when MRE100.RECTYQ = '14' then MSK500.S5MV14
         when MRE100.RECTYQ = '15' then MSK500.S5MV15
         ELSE NULL
    END,
    Case when MRE100.RECTYQ = '16' then MSK500.S5MV16
         when MRE100.RECTYQ = '17' then MSK500.S5MV17 
         else 0
    end,
    case when MRE100.RECTYQ ='U' then MSK500.S5MVTT
         else 0
    end
)

问题：

使用第二个表达式，我只能得到第一个条件的结果：

when MRE100.RECTYQ = 'T' then MGC100.GNTIND

一些图片可能更清晰，

它应该是这样的：

但事实证明：

标签： sqlsql-servertsql

你的主要问题是第二个CASE表达式以一个ELSE条件结束，0如果没有满足所有其他条件。因此，COALESCE从不使用第三个表达式。

底线，我认为你把事情复杂化了。CASE一旦满足匹配条件，就停止评估条件。就您不需要的问题而言COALESCE，您的代码可以表示为单个CASE表达式，例如：

CASE MRE100.RECTYQ
    WHEN 'T'  THEN MGC100.GNTIND 
    WHEN '01' THEN MSK500.S5MV01
    WHEN '02' THEN MSK500.S5MV02
    WHEN '03' THEN MSK500.S5MV03
    WHEN '04' THEN MSK500.S5MV04
    WHEN '05' THEN MSK500.S5MV05
    WHEN '06' THEN MSK500.S5MV06
    WHEN '07' THEN MSK500.S5MV07
    WHEN '08' THEN MSK500.S5MV08
    WHEN '09' THEN MSK500.S5MV09
    WHEN '10' THEN MSK500.S5MV10
    WHEN '11' THEN MSK500.S5MV11
    WHEN '12' THEN MSK500.S5MV12
    WHEN '13' THEN MSK500.S5MV13
    WHEN '14' THEN MSK500.S5MV14
    WHEN '15' THEN MSK500.S5MV15
    WHEN '16' THEN MSK500.S5MV16
    WHEN '17' THEN MSK500.S5MV17 
    WHEN 'U'  THEN MSK500.S5MVTT
    ELSE 0
END

sql - 用 COALESCE 重写 CASE WHEN 语句后出错

问题描述

解决方案

推荐阅读