为一个值检查多列 [英] Checking multiple columns for one value

问题描述

我有一个表格，其中包含这样的列，例如:

id,col1,col2,col3,col4

现在，我想检查 col1, col2, col3, col4 的 ANY 是否有传入的值.

很长的路要走...

SELECT * FROM table WHERE (col1 = 123 OR col2 = 123 OR col3 = 123 OR col4 = 123);

我猜它是 IN 的相反版本.

有没有更简单的方法来做我想做的事?

解决方案

您可以使用 IN 谓词，如下所示:

SELECT * FROM table WHERE 123 IN(col1, col2, col3, col4);

I have a table that has columns like this for example:

id,col1,col2,col3,col4

Now, I want to check if ANY of col1, col2, col3, col4 have the passed in value.

The long way to do it would be..

SELECT * FROM table WHERE (col1 = 123 OR col2 = 123 OR col3 = 123 OR col4 = 123);

I guess it's the opposite version of IN.

Is there an easier way to do what I want?

解决方案

You can use the IN predicate, like so:

SELECT * FROM table WHERE 123 IN(col1, col2, col3, col4);

SQL Fiddle Demo

---

it's the opposite version of IN.

No it is not, It is the same as using the ORs the way you did in your question.

---

To clarify this:

The predicate IN or set membership is defined as¹:

Where the Value Expression can be either ²:

So it is fine to do it this way, using the value expression 123, which is a literal.

---

^{1, 2: Images from: SQL Queries for Mere Mortals(R): A Hands-On Guide to Data Manipulation in SQL}

这篇关于为一个值检查多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！