SELECT CASE语句

Question

这里我有几个基于acctype的doctype。就像下面的sql。

CASE docType 
   WHEN 'I' THEN 'INVOICE' 
   WHEN 'M' THEN 'INVOICE' 
   WHEN 'D' THEN 'DEBIT NOTE' 
   WHEN 'C' THEN 'CREDIT NOTE' 
   WHEN 'P' THEN 'SUPPLIER INVOICE' 
   WHEN 'N' THEN 'SUPPLIER CREDIT NOTE' 
   ELSE '' 
END as docType

我想要如何区分，if doctype = I,M,D,C = Customer, else = supplier

我试着这样做

SELECT
    CASE 
       WHEN (docType = 'I' OR docType = 'M' OR docType = 'D' OR docType = 'C') 
          THEN 'CUSTOMER' 
          ELSE 'SUPPLIER' 
    END as accType 
FROM
    AccountDoc

但是结果不会过滤acctype。

Answer 1

您可以像这样查询：

SELECT CASE  
  WHEN docType IN ('I', 'M', 'D', 'C')
        THEN 'CUSTOMER'
        ELSE 'SUPPLIER'
        END as accType
  FROM AccountDoc

Answer 2

尝尝这个

select (case when (docType = 'I' OR docType = 'M' OR docType = 'D' OR docType = 'C')
    then 'Customer'
    else 'Supplier' end )
    as accType 
from AccountDoc