2023年6月21日发(作者：)
SQL中多条件查询括号的⽤途界⾯:
代码select id,routeName,routeCharacteristic,routeIntroductions,costDetail,participate,click,routeCategory,dineMenu,weather,isEnable,addPerson,addDate,competitiveProducts,luxury,onVacation,characteriswhere (addDate>=@beginDate or @beginDate='') and (addDate<=@endDate or @endDate='')and (routeName like @routeName or @routeName='')and (newProduct=1 or competitiveProducts=1 or luxury=1 or onVacation=1 or characteristic=1or specialPreference=1 or hotRecommend=1 or overflow=1 or season=1)and (priority=0 or priority is null)
以上语句是⼀个多条件查询语句,假如没有⽤括号分开的话,那么只有所有条件都为真才会查出数据库中所满⾜条件的⾏,但是这不是我所想要的,所以才需要⽤括号如:(addDate>=@beginDate or @beginDate=''),意思是当⽤户输⼊数据时,则根据addDate>=@beginDate的条件来查询数据库中满⾜条件的数据,如果⽤户不输⼊任何数据,那么这个时候⽂本框所得到的值则为:""-->空的字符串,使⽤@beginDate='' (变成sql语句:' '=' ' )也能使这个where⼦句满⾜条件,否则的话很难实现像这样的多条件语句查询.
在来看下这条⼦句:(newProduct=1 or competitiveProducts=1 or luxury=1 or onVacation=1 or characteristic=1or specialPreference=1 or hotRecommend=1 or overflow=1 or season=1)这⾥加括号的意思是当满⾜前⾯⼦句所有条件的情况下并且还要满⾜括号内这些字段⾄少有⼀个为1的数据.
(priority=0 or priority is null)这⾥加括号的意思是当满⾜前⾯⼦句所有条件的情况下并且还要满⾜括号priority=0或者priority为空的数据
如果在⼦句:(priority=0 or priority is null)中不加括号的话,那么priority前满⾜所有的条件下,在使⽤or priority is null这样就不是我们要的数据了.这条⼦句加括号也是和上⾯⼦句同⼀个意思(newProduct=1 or competitiveProducts=1 or luxury=1 or onVacation=1 or characteristic=1or specialPreference=1 or hotRecommend=1 or overflow=1 or season=1)
使⽤括号的⽬的就是将⼀⼩段sql⼦句作为⼀个整体来使⽤.简单的说就是在满⾜前⾯所有⼦句的情况下还要满⾜(priority=0 or priority is null)返回为ture的数据.
得到⽂本中输⼊的值调⽤后台数据库代码 private void PriorityBinds() { yNames = new String[] { "id" }; urce = tyQuery(edValue,, ,,edValue); nd(); }
代码 public DataTable PriorityQuery(String MenuType, String beginDate, String endDate, String routeName, String priority) { StringBuilder strSql = new StringBuilder(); (" select id,routeName,routeCharacteristic,routeIntroductions,costDetail,participate,click,routeCategory,dineMenu,weather,isEnable,addPerson,addDate,competitiveProducts,luxury (" from Tab_TouristTrack "); (" where (addDate>=@beginDate or @beginDate='') and (addDate<=dateadd(dd,1,@endDate) or @endDate='')"); (" and (routeName like @routeName or @routeName='')"); if (MenuType != "all") { (" and "+MenuType + "=1"); } else { (" and (newProduct=1 or competitiveProducts=1 or luxury=1 or onVacation=1 or characteristic=1"); (" or specialPreference=1 or hotRecommend=1 or overflow=1 or season=1)"); } if (priority!="all") { if (priority=="1") { (" and priority=1"); } else { (" and (priority=0 or priority is null)"); } } (" order by priority desc"); SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@beginDate",beginDate), new SqlParameter("@endDate",endDate), new SqlParameter("@routeName","%"+routeName+"%") }; return (ng(),param).Tables[0]; }
分类: ,10