MySQL 用于选择多条记录的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9181775/
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
SQL Query for Selecting Multiple Records
提问by user823911
I have 9 fields and I need to see all the data from these fields which have a particular set of IDs. Could any one tell me the SQL query for it?
我有 9 个字段，我需要查看这些字段中具有一组特定 ID 的所有数据。谁能告诉我它的 SQL 查询？
Ex: Database contains 100 records. I need to select a list of 20 IDs from the field BusID and it's corresponding rows.
例如：数据库包含 100 条记录。我需要从字段 BusID 中选择一个包含 20 个 ID 的列表及其对应的行。
SELECT *
FROM `Buses`
WHERE `BusID` I am stuck after this.. how do I put in the list of 20 BusIds here?
回答by TheBoyan
If you know the list of ids try this query:
如果您知道 ID 列表，请尝试以下查询：
SELECT * FROM `Buses` WHERE BusId IN (`list of busIds`)
or if you pull them from another table list of busIdscould be another subquery:
或者如果您从另一个表中提取它们list of busIds可能是另一个子查询：
SELECT * FROM `Buses` WHERE BusId IN (SELECT SomeId from OtherTable WHERE something = somethingElse)
If you need to compare to another table you need a join:
如果您需要与另一个表进行比较，则需要加入：
SELECT * FROM `Buses` JOIN OtheTable on Buses.BusesId = OtehrTable.BusesId
回答by Kamran Ali
You can try this
SELECT * FROM BusesWHERE BusIDin (1,2,3,4,...)
你可以试试这个
SELECT * FROM BusesWHERE BusIDin (1,2,3,4,...)
回答by Michael Berkowski
You're looking for the IN()clause:
您正在寻找的IN()条款：
SELECT * FROM `Buses` WHERE `BusID` IN (1,2,3,5,7,9,11,44,88,etc...);
回答by ComputerSaysNo
I strongly recommend using lowercase field|column names, it will make your life easier.
我强烈建议使用小写字段|列名，它会让你的生活更轻松。
Let's assume you have a table called users with the following definition and records:
假设您有一个名为 users 的表，其中包含以下定义和记录：
id|firstname|lastname|username |password
1 |joe |doe |[email protected] |1234
2 |jane |doe |[email protected] |12345
3 |johnny |doe |[email protected]|123456
let's say you want to get all records from table users, then you do:
假设您想从表用户获取所有记录，然后您执行以下操作：
SELECT * FROM users;
Now let's assume you want to select all records from table users, but you're interested only in the fields id, firstname and lastname, thus ignoring username and password:
现在让我们假设您要从表 users 中选择所有记录，但您只对字段 id、firstname 和 lastname 感兴趣，因此忽略了用户名和密码：
SELECT id, firstname, lastname FROM users;
Now we get at the point where you want to retrieve records based on condition(s), what you need to do is to add the WHERE clause, let's say we want to select from users only those that have username = [email protected] and password = 1234, what you do is:
现在我们要根据条件检索记录，您需要做的是添加 WHERE 子句，假设我们只想从用户中选择用户名 = joe.doe@email 的用户.com 和 password = 1234，你要做的是：
SELECT * FROM users
WHERE ( ( username = '[email protected]' ) AND ( password = '1234' ) );
But what if you need only the id of a record with username = [email protected] and password = 1234? then you do:
但是如果您只需要用户名 = [email protected] 和密码 = 1234 的记录的 id 呢？然后你做：
SELECT id FROM users
WHERE ( ( username = '[email protected]' ) AND ( password = '1234' ) );
Now to get to your question, as others before me answered you can use the IN clause:
现在来回答你的问题，正如我之前的其他人回答的那样，你可以使用 IN 子句：
SELECT * FROM users
WHERE ( id IN (1,2,..,n) );
or, if you wish to limit to a list of records between id 20 and id 40, then you can easily write:
或者，如果您希望限制为 id 20 和 id 40 之间的记录列表，那么您可以轻松地编写：
SELECT * FROM users
WHERE ( ( id >= 20 ) AND ( id <= 40 ) );
I hope this gives a better understanding.
我希望这能提供更好的理解。
回答by M-A Charlotte
Try the following code:
试试下面的代码：
SELECT *
FROM users
WHERE firstname IN ('joe','jane');
回答by Taryn
You want to add the IN()clause to your WHERE
您想将该IN()条款添加到您的WHERE
SELECT *
FROM `Buses`
WHERE `BusID` IN (Id1, ID2, ID3,.... put your ids here)
If you have a list of Ids stored in a table you can also do this:
如果您有一个存储在表中的 Id 列表，您也可以这样做：
SELECT *
FROM `Buses`
WHERE `BusID` IN (SELECT Id FROM table)
回答by Vinay Guru
I have 3 fields to fetch from Oracle Database,Which is for Forex and Currency Application.
我有 3 个字段要从 Oracle 数据库中获取，用于外汇和货币应用程序。
SELECTBUY.RATE FROMFRBU.CURRENCY WHERECURRENCY.MARKET =10 AND CURRENCY.CODE IN (‘USD', 'AUD', ‘SGD')
从FRBU.CURRENCY WHERECURRENCY.MARKET =10 和 CURRENCY.CODE 中选择BUY.RATE ('USD', 'AUD', 'SGD')
