选择其中一个字段属于预定义集合的行 [英] Selecting Rows whose one field of them belongs to a predefined Collection

问题描述

你好，

我的(访问)表具有一个名为SeatNo的字段.我想返回其SeatNo是以下集合的成员的所有行.当集合很大时，不可能在SELECT命令中一一写入.您能帮我找到所需的SQL命令吗?

设置为:1，5,10,15,20，...，500

非常感谢

Hello,

My (Access) Table has a field called SeatNo . I want to return all rows whose SeatNo is a member of the following set. When the set is large, it is impossible to write them one by one in SELECT command. Could you please help me to find the needed SQL command?

Set is: 1, 5,10,15,20,..., 500

Thanks a lot

推荐答案

，可以通过在选择命令中添加"IN"运算符来实现..
这样

选择*从年龄在(24,25,23,26)的EMP中

从"EMP"表中选择其年龄为"AGE"的记录在集合中

24,25,23和26(平均年龄等于24,25，23或26).

如果您的搜索列是字符类型...意味着您需要为值提供单引号，然后使用此...

SELECT * FROM EMP WHERE NAME IN(``TARZAN''，``DON'')

希望对您有帮助....

您可以像这样使您的命令

that can be achieved from adding "IN" operator in your select command..
as this

SELECT * FROM EMP WHERE AGE IN (24,25,23,26)

selects the records from "EMP" table whose ages "AGE" are in the Collection

24,25,23 and 26(means age is equal to 24,25, 23 or 26)..

if your searching Column is of type character... means you need to give the single quotation for your values then use this...

SELECT * FROM EMP WHERE NAME IN (''TARZAN'',''DON'')

hope this helps you....

you can make your Command like this

OledbCommand oledbCmd = new OledbCommand();
oledbCmd.Connection = myConnection;
string command = "select * from emp where id in (";

for(int i=0; i<valueCollection.Length; i++)/*assuming that ''valueCollection'' is your collection of values..*/
{
     command = command + "@" + i.ToString() ;/*adding parameters to command string*/
     if(i <valueCollection-1)/*if the present item valueCollection[i] is not the last item then add '','' to the command string*/
          command = command + ",";
     else /*else add '')'' to the command string*/
          command = command + ")";
     oledbCmd.Parameters.Add("@" + i.ToString(), OledbType.Integer).Value = valueCollection[i].ToString();  /*giving the values to the Parameters what we added in the command string using "@"*/
}
OleDbDataAdapter ad = new OledbDataAdapter();
ad.SelectCommand = oledbCmd;
DataSet ds = new DataSet();
ad.Fill(ds,"emp");
//you know after this..

这篇关于选择其中一个字段属于预定义集合的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！