为一个值检查多个列

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.

现在，我要检查col1, col2, col3, col4是否有传递值。

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.

我猜是IN的反义词。

Is there an easier way to do what I want?

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

3 个解决方案

#1

You can use the IN predicate, like so:

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

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

SQL Fiddle Demo

---

it's the opposite version of IN.

它是IN的反义词。

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

不，它不是，它和你在你的问题中使用ORs是一样的。

---

To clarify this:

The predicate IN or set membership is defined as¹:

集合成员中的谓词定义为:

Where the Value Expression can be either ²:

其中值表达式可以为2:

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

所以可以这样做，使用值表达式123，这是一个文字。

---

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

1、2:图片来自:凡人的SQL查询(R): SQL中数据操作的实践指南

#2

You could do something like: (Note: Assuming the columns are numeric values. And, just incase the concatenated value creates the character sequence you are looking for, use a delimiter to distinguish the column values. Pipe (|) is the delimiter in this example.)

您可以这样做:(注意:假设列是数值。并且，仅当连接的值创建您要查找的字符序列时，使用分隔符来区分列值。管道(|)是本例中的分隔符。

SELECT [ID]
    ,[Col1]
    ,[Col2]
    ,[Col3]
    ,[Col4]
FROM [Table1]
WHERE '123' IN (
    CAST([Col1] AS VARCHAR) + '|'
    + CAST([Col2] AS VARCHAR) + '|'
    + CAST([Col3] AS VARCHAR) + '|'
    + CAST([Col4] AS VARCHAR) + '|'
)

#3

I had a similar problem and solved it this way: SELECT * FROM table WHERE col1 OR col2 IN(SELECT xid FROM tablex WHERE somecol = 3)

我遇到了类似的问题，并通过以下方式解决:从col1或col2所在的表中选择*(从somecol = 3的表中选择xid)

Not sure if it is "the best way" but it works for me.

我不确定这是否是“最好的方式”，但对我来说是可行的。

Thoughts?

想法吗?