用于选择多个记录的SQL查询

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?

7 个解决方案

#1

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 busIds could be another subquery:

或者如果你从另一个表中提取它们的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

#2

You can try this
SELECT * FROM Buses WHERE BusID in (1,2,3,4,...)

你可以尝试这个SELECT * FROM Buses WHERE BusID in（1,2,3,4，...）

#3

You're looking for the IN() clause:

你正在寻找IN（）子句：

SELECT * FROM `Buses` WHERE `BusID` IN (1,2,3,5,7,9,11,44,88,etc...);

#4

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     |joe.doe@email.com   |1234
2 |jane     |doe     |jane.doe@email.com  |12345
3 |johnny   |doe     |johnny.doe@email.com|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:

现在让我们假设您要从表用户中选择所有记录，但您只对字段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 = joe.doe@email.com and password = 1234, what you do is:

现在我们想要根据条件检索记录，你需要做的是添加WHERE子句，假设我们只想从用户中选择那些有username = joe.doe@email的用户.com和密码= 1234，你要做的是：

SELECT * FROM users
WHERE ( ( username = 'joe.doe@email.com' ) AND ( password = '1234' ) );

But what if you need only the id of a record with username = joe.doe@email.com and password = 1234? then you do:

但是，如果您只需要用户名= joe.doe@email.com和密码= 1234的记录的ID，该怎么办？然后你做：

SELECT id FROM users
WHERE ( ( username = 'joe.doe@email.com' ) 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.

我希望这能让你有更好的理解。

#5

Try the following code:

请尝试以下代码：

SELECT *
FROM users
WHERE firstname IN ('joe','jane');

#6

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)

#7

I have 3 fields to fetch from Oracle Database,Which is for Forex and Currency Application.

我从Oracle数据库中获取了3个字段，用于外汇和货币应用程序。

SELECT BUY.RATE FROM FRBU.CURRENCY WHERE CURRENCY.MARKET =10 AND CURRENCY.CODE IN (‘USD’, ’AUD’, ‘SGD’)

选择BUY.RATE来自FRBU.CURRENCY，其中CURRENCY.MARKET = 10和CURRENCY.CODE IN（'USD'，'AUD'，'SGD'）

#1