如何改进使用两列的 case 语句 [英] How to improve a case statement that uses two columns

问题描述

我有一个名为 Purchase 的表，它有一个 State 列，其中 1 已授权，2 已完成(还有一些其他值).

I have a table called Purchase which has a State column, where 1 is authorized, 2 is completed (there are some other values too).

我还有一个 Retailer 表，其中有一个 RetailerProcessType 列，其中 1 是一步，2 是两步.

I also have a Retailer table, which has a column RetailerProcessType, where 1 is one-step and 2 is two-step.

我有以下查询:

CASE purc.State
    WHEN 1 THEN '"AUTHORISED"'
    WHEN 2 THEN '"AUTHORISED"'
    WHEN 4 THEN '"AUTHORISED"'
    ELSE '"DECLINED"'
 END                                                                     
 AS Autorised_Decline_Status,

但我需要做的是:

WHEN STATE = 2 AND RetailerProcessType = 1 THEN '"AUTHORISED"'
WHEN STATE = 1 AND RetailerProcessType = 2 THEN '"PENDING"'
WHEN STATE = 2 AND RetailerProcessType = 2 THEN '"AUTHORISED"'
ELSE '"DECLINED"'

我能想到的唯一方法是在查询周围使用大量 IF 语句，一个用于一步零售商，另一个用于两步，因为我的理解是 IFcode>WHEN 子句中不能包含 AND.

The only way I can think of doing this is having a massive IF statement around the query, one for a one-step retailer and another for a two-step, as my understanding is a WHEN clause cannot have an AND in it.

然而，这似乎是冗长的；有人有更好的想法吗?

However, this just seems long winded; anybody have any neater ideas?

推荐答案

你可以这样做:

-- Notice how STATE got moved inside the condition:
CASE WHEN STATE = 2 AND RetailerProcessType IN (1, 2) THEN '"AUTHORISED"'
     WHEN STATE = 1 AND RetailerProcessType = 2 THEN '"PENDING"'
     ELSE '"DECLINED"'
END

您可以在此处执行 AND 的原因是您没有检查 STATE 的 CASE，而是检查 CASING 条件.

The reason you can do an AND here is that you are not checking the CASE of STATE, but instead you are CASING Conditions.

这里的关键部分是 STATE 条件是 WHEN 的一部分.

The key part here is that the STATE condition is a part of the WHEN.

这篇关于如何改进使用两列的 case 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！