选择其中的位置 - mySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18039565/
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
SELECT WHERE IN - mySQL
提问by vshale
let's say I have the following Table:
假设我有下表：
ID, Name
1, John
2, Jim
3, Steve
4, Tom
I run the following query
我运行以下查询
SELECT Id FROM Table WHERE NAME IN ('John', 'Jim', 'Bill');
I want to get something like:
我想得到类似的东西：
ID
1
2
NULL or 0
Is it possible?
是否可以？
采纳答案by Ed Gibbs
Start by creating a subquery of names you're looking for, then left join the subquery to your table:
首先创建您要查找的名称的子查询，然后将子查询左连接到您的表：
SELECT myTable.ID
FROM (
SELECT 'John' AS Name
UNION SELECT 'Jim'
UNION SELECT 'Bill'
) NameList
LEFT JOIN myTable ON NameList.Name = myTable.Name
This will return nullfor each name that isn't found. To return a zero instead, just start the query with SELECT COALESCE(myTable.ID, 0)instead of SELECT myTable.ID.
这将为null每个未找到的名称返回。要改为返回零，只需使用SELECT COALESCE(myTable.ID, 0)而不是开始查询SELECT myTable.ID。
There's a SQL Fiddle here.
有一个SQL小提琴这里。
回答by ChaiNavawongse
How about this?
这个怎么样？
SELECT Id FROM Table WHERE NAME IN ('John', 'Jim', 'Bill')
UNION
SELECT null;
回答by marco
The question is a bit confusing. "IN" is a valid operator in SQL and it means a match with any of the values (see here):
这个问题有点令人困惑。“IN”是 SQL 中的有效运算符，它表示与任何值匹配（请参见此处）：
SELECT Id FROM Table WHERE NAME IN ('John', 'Jim', 'Bill');
Is the same as:
是相同的：
SELECT Id FROM Table WHERE NAME = 'John' OR NAME = 'Jim' OR NAME = 'Bill';
In your answer you seem to want the replies for each of the values, in order. This is accomplished by joining the results with UNION ALL (only UNION eliminates duplicates and can change the order):
在您的回答中，您似乎希望按顺序回复每个值。这是通过将结果与 UNION ALL 连接来实现的（只有 UNION 消除重复并可以更改顺序）：
SELECT max(Id) FROM Table WHERE NAME = 'John' UNION ALL
SELECT max(Id) FROM Table WHERE NAME = 'Jim' UNION ALL
SELECT max(Id) FROM Table WHERE NAME = 'Bill';
The above will return 1 Id (the max) if there are matches and NULL if there are none (e.g. for Bill). Note that in general you can have more than one row matching some of the names in your list, I used "max" to select one, you may be better of in keeping the loop on the values outside the query or in using the (ID, Name) table in a join with other tables in your database, instead of making the list of ID and then using it.
如果有匹配项，上面将返回 1 Id（最大值），如果没有匹配项，则返回 NULL（例如对于 Bill）。请注意，通常您可以有不止一行匹配列表中的某些名称，我使用“max”来选择一个，您最好保持查询之外的值的循环或使用 (ID , Name) 表与数据库中的其他表进行连接，而不是制作 ID 列表然后使用它。
