带有 ELSe 的嵌套 CASE 语句 (SQL Server)

Nested CASE Statement With ELSe (SQL Server)

提问by InTheWorldOfCodingApplications

I have a CASE statement something like following

我有一个 CASE 语句，如下所示

CASE 
       WHEN A IS NULL 
       THEN CASE
                 WHEN B IN ('C','D') THEN NULL
                 WHEN X NOT IN ('C','D') THEN Z
                 End
                 ELSE SOMETHING_ELSE -- Want to get here When 'A' IS NOT NULL
                 END AS 'Result'

I want to get to ELSE part when First CASE is not true i-e 'A' is not NULL. Can anyone suggest if i have wrongly nested them? not getting results right.

当第一个 CASE 不正确即“A”不为 NULL 时，我想进入 ELSE 部分。谁能建议我是否错误地嵌套了它们？没有得到正确的结果。

Many Thanks,

非常感谢，

回答by Gordon Linoff

First, you don't need to nest casestatements. Just use one case:

首先，您不需要嵌套case语句。只使用一种情况：

select (CASE WHEN A IS NULL AND B IN ('C', 'D') THEN NULL
             WHEN A IS NULL AND X NOT IN ('C','D') THEN Z
             WHEN A IS NOT NULL THEN SOMETHING_ELSE
        END) as Result

Note that when A IS NULLbut the first two conditions are notmet, then the return value will be NULL.

注意，当A IS NULL但前两个条件不能满足，则返回值将是NULL。

Because casestatements are evaluated sequentially, this is simpler to write as:

因为case语句是按顺序计算的，所以写成这样更简单：

select (CASE WHEN A IS NOT NULL THEN SOMETHING_ELSE
             WHEN B IN ('C', 'D') THEN NULL
             WHEN X NOT IN ('C', 'D') THEN Z
        END) as Result

The first condition captures when Ais not NULL. Hence the second two are when Ais NULL.

第一个条件捕获 when Ais not NULL。因此，第二两个是当A是NULL。

回答by Mahesh

You can add the second When to your case where you can check for the second condition and set value otherwise set default value.

您可以将第二个 When 添加到您的案例中，您可以在其中检查第二个条件并设置值，否则设置默认值。

CASE 
    WHEN A IS NULL THEN CASE
                          WHEN B IN ('C','D') THEN NULL
                          WHEN X NOT IN ('C','D') THEN Z
                        End
    WHEN A IS NOT NULL THEN yourdesiredvalue
    ELSE default value
    END AS 'Result'