MySQL 为一个值检查多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13916400/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Checking multiple columns for one value
提问by Brett
I have a table that has columns like this for example:
我有一个表，其中包含这样的列，例如：
id,col1,col2,col3,col4
Now, I want to check if ANYof col1, col2, col3, col4have the passed in value.
现在，我想检查ANY的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?
有没有更简单的方法来做我想做的事？
回答by Mahmoud Gamal
You can use the INpredicate, like so:
您可以使用IN谓词，如下所示：
SELECT * FROM table WHERE 123 IN(col1, col2, col3, col4);
SQL Fiddle Demo
SQL 小提琴演示
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.
不，不是，这与OR您在问题中使用s 的方式相同。
To clarify this:
为了澄清这一点：
The predicate INor set membership is defined as1:
谓词IN或集合成员资格定义为1：
Where the Value Expressioncan be either 2:
其中Value Expression可以是2：
So it is fine to do it this way, using the value expression 123, which is a literal.
所以这样做是可以的，使用 value expression 123，它是一个文字。
1, 2: Images from: SQL Queries for Mere Mortals(R): A Hands-On Guide to Data Manipulation in SQL
回答by Dionicio Gonzalez
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) + '|'
)
回答by Milk Man
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)
我有一个类似的问题，并以这种方式解决了它： SELECT * FROM table WHERE col1 OR col2 IN(SELECT xid FROM tablex WHERE somecol = 3)
Not sure if it is "the best way" but it works for me.
不确定这是否是“最好的方式”，但它对我有用。
Thoughts?
想法？
