为一个值检查多列

问题描述

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

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}

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