SQL Server 中的 CASE 语句不支持 OR

OR is not supported with CASE Statement in SQL Server

提问by Werner

The ORoperator in the WHENclause of a CASEstatement is not supported. How can I do this?

不支持语句子句中的OR运算符。我怎样才能做到这一点？WHENCASE

CASE ebv.db_no 
    WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500' 
    ELSE 'WECS 9520' 
END as wecs_system 

回答by OMG Ponies

That format requires you to use either:

该格式要求您使用：

CASE ebv.db_no 
  WHEN 22978 THEN 'WECS 9500' 
  WHEN 23218 THEN 'WECS 9500'  
  WHEN 23219 THEN 'WECS 9500' 
  ELSE 'WECS 9520' 
END as wecs_system 

Otherwise, use:

否则，请使用：

CASE  
  WHEN ebv.db_no IN (22978, 23218, 23219) THEN 'WECS 9500' 
  ELSE 'WECS 9520' 
END as wecs_system 

回答by Darren

CASE
  WHEN ebv.db_no = 22978 OR 
       ebv.db_no = 23218 OR
       ebv.db_no = 23219
  THEN 'WECS 9500' 
  ELSE 'WECS 9520' 
END as wecs_system 

回答by Cade Roux

CASE WHEN ebv.db_no  IN (22978, 23218, 23219) THEN 'WECS 9500' 
  ELSE 'WECS 9520' 
END as wecs_system 

回答by Alfaplus

You can use one of the expressions that WHEN has, but you cannot mix both of them.

您可以使用 WHEN 具有的表达式之一，但不能混合使用它们。

1. WHEN when_expression

   Is a simple expression to which input_expression is compared when the simple CASE format is used. when_expression is any valid expression. The data types of input_expression and each when_expression must be the same or must be an implicit conversion.

2. WHEN Boolean_expression

   Is the Boolean expression evaluated when using the searched CASE format. Boolean_expression is any valid Boolean expression.

1. WHEN when_expression

   是一个简单的表达式，当使用简单的 CASE 格式时，将与 input_expression 进行比较。when_expression 是任何有效的表达式。input_expression 和每个when_expression 的数据类型必须相同或必须是隐式转换。

2. WHEN 布尔表达式

   是在使用搜索的 CASE 格式时计算的布尔表达式。Boolean_expression 是任何有效的布尔表达式。

You could program:

你可以编程：

1.

1.

    CASE ProductLine
            WHEN 'R' THEN 'Road'
            WHEN 'M' THEN 'Mountain'
            WHEN 'T' THEN 'Touring'
            WHEN 'S' THEN 'Other sale items'
            ELSE 'Not for sale'

2.

2.

    CASE
            WHEN ListPrice =  0 THEN 'Mfg item - not for resale'
            WHEN ListPrice < 50 THEN 'Under '
            WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under 0'
            WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under 00'
            ELSE 'Over 00'
          END

But in any case you can expect that the variable ranking is going to be compared in a boolean expression.

但在任何情况下，您都可以预期变量排名将在布尔表达式中进行比较。

See CASE (Transact-SQL)(MSDN).

请参阅案例 (Transact-SQL)(MSDN)。

回答by Somnath Muluk

There are already a lot of answers with respect to CASE. I will explain when and how to use CASE.

已经有很多关于CASE. 我将解释何时以及如何使用CASE.

You can use CASE expressions anywhere in the SQL queries. CASE expressions can be used within the SELECT statement, WHERE clauses, Order by clause, HAVING clauses, Insert, UPDATE and DELETE statements.

您可以在 SQL 查询中的任何位置使用 CASE 表达式。CASE 表达式可以在 SELECT 语句、WHERE 子句、Order by 子句、HAVING 子句、Insert、UPDATE 和 DELETE 语句中使用。

A CASE expression has the following two formats:

CASE 表达式有以下两种格式：

1. Simple CASE expression

   CASE expression
   WHEN expression1 THEN Result1
   WHEN expression2 THEN Result2
   ELSE ResultN
   END
   

   This compares an expression to a set of simple expressions to find the result. This expression compares an expression to the expression in each WHEN clause for equivalency. If the expression within the WHEN clause is matched, the expression in the THEN clause will be returned.

   This is where the OP's question is falling. 22978 OR 23218 OR 23219will not get a value equal to the expression i.e. ebv.db_no. That's why it is giving an error. The data types of input_expression and each when_expression must be the same or must be an implicit conversion.

2. Searched CASE expressions

   CASE
   WHEN Boolean_expression1 THEN Result1
   WHEN Boolean_expression2 THEN Result2
   ELSE ResultN
   END
   

   This expression evaluates a set of boolean expressions to find the result. This expression allows comparison operators, and logical operators AND/OR with in each Boolean expression.

1. 简单的 CASE 表达式

   CASE expression
   WHEN expression1 THEN Result1
   WHEN expression2 THEN Result2
   ELSE ResultN
   END
   

   这将一个表达式与一组简单的表达式进行比较以找到结果。此表达式将一个表达式与每个 WHEN 子句中的表达式进行比较以获取等价性。如果匹配 WHEN 子句中的表达式，则返回 THEN 子句中的表达式。

   这就是 OP 的问题所在。22978 OR 23218 OR 23219不会得到等于表达式的值，即 ebv.db_no。这就是它给出错误的原因。input_expression 和每个when_expression 的数据类型必须相同或必须是隐式转换。

2. 搜索 CASE 表达式

   CASE
   WHEN Boolean_expression1 THEN Result1
   WHEN Boolean_expression2 THEN Result2
   ELSE ResultN
   END
   

   此表达式计算一组布尔表达式以找到结果。此表达式允许在每个布尔表达式中使用比较运算符和逻辑运算符 AND/OR。

1.SELECT statement with CASE expressions

1.SELECT 语句与 CASE 表达式

--Simple CASE expression: 
SELECT FirstName, State=(CASE StateCode
 WHEN 'MP' THEN 'Madhya Pradesh' 
 WHEN 'UP' THEN 'Uttar Pradesh' 
 WHEN 'DL' THEN 'Delhi' 
 ELSE NULL 
 END), PayRate
FROM dbo.Customer

-- Searched CASE expression:
SELECT FirstName,State=(CASE 
 WHEN StateCode = 'MP' THEN 'Madhya Pradesh' 
 WHEN StateCode = 'UP' THEN 'Uttar Pradesh' 
 WHEN StateCode = 'DL' THEN 'Delhi' 
 ELSE NULL 
 END), PayRate
FROM dbo.Customer

2.Update statement with CASE expression

2.用CASE表达式更新语句

-- Simple CASE expression: 
UPDATE Customer 
SET StateCode = CASE StateCode
 WHEN 'MP' THEN 'Madhya Pradesh' 
 WHEN 'UP' THEN 'Uttar Pradesh' 
 WHEN 'DL' THEN 'Delhi' 
 ELSE NULL 
 END 

-- Simple CASE expression: 
UPDATE Customer 
SET StateCode = CASE 
 WHEN StateCode = 'MP' THEN 'Madhya Pradesh' 
 WHEN StateCode = 'UP' THEN 'Uttar Pradesh' 
 WHEN StateCode = 'DL' THEN 'Delhi' 
 ELSE NULL 
 END 

3.ORDER BY clause with CASE expressions

3.ORDER BY 子句与 CASE 表达式

-- Simple CASE expression: 
SELECT * FROM dbo.Customer
ORDER BY 
 CASE Gender WHEN 'M' THEN FirstName END Desc,
 CASE Gender WHEN 'F' THEN LastName END ASC

-- Searched CASE expression: 
SELECT * FROM dbo.Customer
ORDER BY 
 CASE WHEN Gender='M' THEN FirstName END Desc,
 CASE WHEN Gender='F' THEN LastName END ASC

4.Having Clause with CASE expression

4.Hawing CASE 表达式的子句

-- Simple CASE expression: 
SELECT FirstName ,StateCode,Gender, Total=MAX(PayRate)
FROM dbo.Customer
GROUP BY StateCode,Gender,FirstName
HAVING (MAX(CASE Gender WHEN 'M' 
 THEN PayRate 
 ELSE NULL END) > 180.00
 OR MAX(CASE Gender WHEN 'F' 
 THEN PayRate 
 ELSE NULL END) > 170.00)

-- Searched CASE expression: 
SELECT FirstName ,StateCode,Gender, Total=MAX(PayRate)
FROM dbo.Customer
GROUP BY StateCode,Gender,FirstName
HAVING (MAX(CASE WHEN Gender = 'M' 
 THEN PayRate 
 ELSE NULL END) > 180.00
 OR MAX(CASE WHEN Gender = 'F' 
 THEN PayRate 
 ELSE NULL END) > 170.00)

Hope this use cases will help someone in future.

希望这个用例将来会对某人有所帮助。

Source

来源

回答by JNK

Try

尝试

CASE WHEN ebv.db_no IN (22978,23218,23219) THEN 'WECS 9500' ELSE 'WECS 9520' END

回答by Archu

SELECT
  Store_Name,
  CASE Store_Name
    WHEN 'Los Angeles' THEN Sales * 2
    WHEN 'San Diego' THEN Sales * 1.5
    ELSE Sales
    END AS "New Sales",
  Txn_Date
FROM Store_Information;

回答by Anand agrawal

UPDATE table_name 
  SET column_name=CASE 
WHEN column_name in ('value1', 'value2',.....) 
  THEN 'update_value' 
WHEN column_name in ('value1', 'value2',.....) 
  THEN 'update_value' 
END

table_name= The name of table on which you want to perform operation.

table_name= 要对其执行操作的表的名称。

column_name= The name of Column/Field of which value you want to set.

column_name= 要设置其值的列/字段的名称。

update_value= The value you want to set of column_name

update_value= 要设置的值 column_name

回答by Debendra Dash

select id,phno,case gender
when 'G' then 'M'
when 'L' then 'F'
else
'No gender'
end
as gender 
from contacts

回答by Mohammad Shahnawaz

Select s.stock_code,s.stock_desc,s.stock_desc_ar,
mc.category_name,s.sel_price,
case when s.allow_discount=0 then 'Non Promotional Item' else 'Prmotional 
item' end 'Promotion'
From tbl_stock s inner join tbl_stock_category c on s.stock_id=c.stock_id
inner join tbl_category mc on c.category_id=mc.category_id
where mc.category_id=2 and s.isSerialBased=0