例如我现在在数据库中有这么几条数据:学号,姓名,班级,班主任ID,出生日期
我在查询学生信息界面中要求可以根据以上5条数据中的任意一项或多项查询出数据
我自己写的sql语句拼接如下: string strsql = "select * from stu where ";
if (stuNum != "")
{
strsql += "stuNum like '%" + stuNum + "%' and ";
}
else
{
strsql += "stuNum like '%%' and ";
}
if (stuName != "")
{
strsql += "stuName ='" + stuName + "' and ";
}
if (beginDate != "")
{
strsql += "createdate>=to_date('" + beginDate + " 00:00:00 " + "','yyyy-mm-dd hh24:mi:ss')" + " and ";
}
if (endDate != "")
{
strsql += "createdate<=to_date('" + endDate + " 23:59:59 " + "','yyyy-mm-dd hh24:mi:ss')" + " and ";
}
if (type != "")
{
strsql += "type ='" + type + "' and ";
}
if (consumer != "")
{
strsql += "consumer like '%" + consumer + "%' ";
}
if (strsql.EndsWith("and "))
{
strsql = strsql.Remove(strsql.LastIndexOf("and"), 4);
}但这么写感觉每次都要判断用户填写的查询条件是否为空,十分罗嗦,而且貌似会影响速度......
求简化方法~~~~~~~~
我在查询学生信息界面中要求可以根据以上5条数据中的任意一项或多项查询出数据
我自己写的sql语句拼接如下: string strsql = "select * from stu where ";
if (stuNum != "")
{
strsql += "stuNum like '%" + stuNum + "%' and ";
}
else
{
strsql += "stuNum like '%%' and ";
}
if (stuName != "")
{
strsql += "stuName ='" + stuName + "' and ";
}
if (beginDate != "")
{
strsql += "createdate>=to_date('" + beginDate + " 00:00:00 " + "','yyyy-mm-dd hh24:mi:ss')" + " and ";
}
if (endDate != "")
{
strsql += "createdate<=to_date('" + endDate + " 23:59:59 " + "','yyyy-mm-dd hh24:mi:ss')" + " and ";
}
if (type != "")
{
strsql += "type ='" + type + "' and ";
}
if (consumer != "")
{
strsql += "consumer like '%" + consumer + "%' ";
}
if (strsql.EndsWith("and "))
{
strsql = strsql.Remove(strsql.LastIndexOf("and"), 4);
}但这么写感觉每次都要判断用户填写的查询条件是否为空,十分罗嗦,而且貌似会影响速度......
求简化方法~~~~~~~~
if (stuNum != "")
{
strsql += "stuNum like '%" + stuNum + "%' and ";
//可以改为用strsql += " and charindex('" + stuNum + "',stuNum)>0";提高点速度
}
else
{
strsql += "stuNum like '%%' and "; //为啥要要?
}string strsql = "select * from stu where 1=1";sqlstr += " and ..=''";
sqlstr += " and ..=''";
sqlstr += " and ..=''";
sqlstr += " and ..=''";if (strsql.EndsWith("and "))
{
strsql = strsql.Remove(strsql.LastIndexOf("and"), 4);
}这个就可以不要了,而且避免了where后可能没条件
string strsql = "select * from stu where 1=1 ";
if (stuNum != "")
{
strsql += " and stuNum like '%" + stuNum + "%' ";
}
if (stuName != "")
{
strsql += " and stuName ='" + stuName;
}
if (beginDate != "")
{
strsql += " and createdate>=to_date('" + beginDate + " 00:00:00 " + "','yyyy-mm-dd hh24:mi:ss')";
}
if (endDate != "")
{
strsql += " and createdate<=to_date('" + endDate + " 23:59:59 " + "','yyyy-mm-dd hh24:mi:ss')" ;
}
if (type != "")
{
strsql += " and type ='" + type ;
}
if (consumer != "")
{
strsql += " and consumer like '%" + consumer + "%' ";
}
else
{
strsql += "stuNum like '%%' and "; //为啥要要?
}
//这句是判断sql语句最后是否存在"and",例如当查询条件只有strName时,sql语句为"select * from stu where stuName ='张三' and ",这时需要将结尾处"and "去掉
if (strsql.EndsWith("and "))
{
strsql = strsql.Remove(strsql.LastIndexOf("and"), 4);
}这个就可以不要了,而且避免了where后可能没条件