如何在where子句中获得多个条件

I want to run a query on some fields of the table, but I cant understand how to do it. I have the condition that first I need to check value of col1, if it is 'W', then I need to check colA having values in ('1','2'), if it is not 'W', then I need to check colB having values in ('1','2'). This condition to check ('1','2') applies same to both colA and colB, just depending on the value of col1.

我想在表的某些字段上运行查询，但我不明白如何执行。我有一个条件，首先我需要检查col1的值，如果是W，那么我需要检查可乐的值在(1,2)中，如果不是W，那么我需要检查colB的值在(1,2)中。检查条件('1'、'2')对可乐和可乐都适用，这取决于可乐1的值。

I tried using this, but obviously this is incorrect.

我试过用这个，但显然这是错误的。

SELECT * FROM tbl
WHERE CASE WHEN col1= 'W' THEN (colA IN ('1','2')) ELSE (colB IN ('1','2')) END

So how can I do it in a single query without using Unions etc.

如何在一个查询中完成，而不使用联合等等。

2 个解决方案

#1

Sql Server-2014 : Use CASE to choose column to be used in WHERE clause based on condition-

Sql Server-2014:用例根据条件-选择WHERE子句中要使用的列

SELECT * FROM tab1 
WHERE 
    (CASE WHEN col1= 'W' THEN colA ELSE colB END) in ('1', '2');

Tested on SqlServer-2014. image attached

测试- 2014状态"置疑"。附加图片

MySql : Use IF() function to choose column to be used in WHERE clause based on condition-

MySql:使用IF()函数根据条件-选择WHERE子句中要使用的列

SELECT * FROM tab1 
WHERE 
    IF(col1='W', colA, colB) in ('1', '2');

#2

You don't need CASE, just put all your boolean logic together in one expression.

您不需要大小写，只需将所有布尔逻辑放在一个表达式中。

SELECT *
FROM T
WHERE
    (Col1 = 'W' AND ColA IN ('1', '2'))
    OR
    (Col1 <> 'W' AND ColB IN ('1', '2'))
;

#1