案例陈述MySql

Question

我试图为下表结构编写一个案例语句：

========================================
tender_cust_shortcode | open_auth_date
========================================
HAL Hydera            |   24-01-2016
DRDL                  |   NULL
HAL ARDC              |   10-02-2016
DLRL                  |   NULL
ISAC                  |   NULL
========================================

我的案例陈述如下：

CASE WHEN (tender_cust_shortcode = 'HAL Hydera' OR 'HAL ARDC') THEN 'N/A'
     WHEN (tender_cust_shortcode != 'HAL Hydera' OR 'HAL ARDC') AND open_auth_date IS NULL 
THEN 'Pending'  
ELSE 'Completed' END AS `Open Auth Status`

标准是

1)如果(tender_cust_shortcode =‘Hydera’或'HAL‘)，则其结果应为'N/A’。

2)如果(tender_cust_shortcode !=‘Hydera’或'HAL‘)和open_auth_date为空，则其结果应为“未决”。

3)其他“已完成”。

我没有得到想要的输出。可能是和或条件问题。有人能帮我吗？

Answer 1

尝试使用IN，不确定这是否有效，但是尝试一下：

CASE
    WHEN tender_cust_shortcode IN ('HAL Hydera', 'HAL ARDC') THEN 'N/A'
    WHEN tender_cust_shortcode NOT IN ('HAL Hydera', 'HAL ARDC') AND open_auth_date IS NULL THEN 'Pending'  
    ELSE 'Completed'
END AS `Open Auth Status`

这是SQLFiddle演示，它起作用了。

Answer 2

您必须重复列名，并且必须由AND连接的或不相等条件

CASE WHEN (tender_cust_shortcode = 'HAL Hydera' OR tender_cust_shortcode = 'HAL ARDC') THEN 'N/A'
     WHEN (tender_cust_shortcode != 'HAL Hydera' AND tender_cust_shortcode != 'HAL ARDC') AND open_auth_date IS NULL 
THEN 'Pending'  
ELSE 'Completed' END AS `Open Auth Status`

请参阅SQLFiddle

Answer 3

尝试这样的查询：

SELECT CASE WHEN (tender_cust_shortcode = 'HAL Hydera' OR 'HAL ARDC') THEN 'N/A'
WHEN ((tender_cust_shortcode != 'HAL Hydera' OR 'HAL ARDC') AND (open_auth_date IS NULL)) THEN 'Pending'
ELSE 'Completed' END FROM `test`