C# SQL 筛选

来源：互联网发布：linux ibus输入法编辑：程序博客网时间：2024/05/16 10:20

一、查询两张表，从第一张表中按nutCode.id排出第二张表内已有的内容

方法一：

string selectstr =“select carType.carTypeName,nutCode.code,nutCode.nutCodeName,nutCode.id ”+

“from nutCode,carType ”+

“where nutCode.carTypeId=carType.id and nutCode.isPtest=1 and nutCode.id ”+

“not in(select taskContent.nutCodeid from taskContent,task where taskContent.taskId=task.id and task.cdatetime>='2011-12-12 08:08:08')”;

方法二：

string selectstr = "select carType.carTypeName,nutCode.code,nutCode.nutCodeName,nutCode.id " +
"from carType,nutCode " +
"where nutCode.carTypeId=carType.id and nutCode.isPtest=1 ";

SqlCommand selectcmd = new SqlCommand(selectstr , conn);
SqlDataAdapter selectsda = new SqlDataAdapter();
selectsda.SelectCommand = selectcmd;
DataSet ds = new DataSet();
selectsda.Fill(ds, "box");
//连接数据库
//查询几个表
string selectstr1 = "select carType.carTypeName,nutCode.nutCodeName,taskContent.completion,taskContent.nutCodeId " +
"from carType,nutCode,taskContent,task " +
"where taskContent.nutCodeId=nutCode.id and nutCode.carTypeId=carType.id and " +
"taskContent.taskId=task.id and task.cdatetime>='2011-12-12 08:08:08'";

SqlCommand selectcmd1 = new SqlCommand(selectstr1 , conn);
SqlDataAdapter selectsda1 = new SqlDataAdapter();
selectsda1.SelectCommand = selectcmd1;
DataSet ds1 = new DataSet();
selectsda1.Fill(ds1, "box");
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
for (int j = 0; j < ds1.Tables[0].Rows.Count; j++)
{
if (ds.Tables[0].Rows[i]["id"].ToString() == ds1.Tables[0].Rows[j]["nutCodeId"].ToString())
{
ds.Tables[0].Rows.RemoveAt(i); //删除ds与ds1中重复的
}
else
{
ds.Tables[0].Rows[i]["number"] = Convert.ToString(i + 1);
}
}
}

二、查询两张表，从第一张表中按nutCode.id查出第二张表内已有的内容

string selectstr =“select carType.carTypeName,nutCode.code,nutCode.nutCodeName,nutCode.id ”+

“from nutCode,carType ”+

“where nutCode.carTypeId=carType.id and nutCode.isPtest=1 and nutCode.id ”+

“in(select taskContent.nutCodeid from taskContent,task where taskContent.taskId=task.id and task.cdatetime>='2011-12-12 08:08:08')”;