SQL中多条件查询括号的用途

界面:

select id,routeName,routeCharacteristic,routeIntroductions,costDetail,participate,click,routeCategory,dineMenu,weather,isEnable,addPerson,addDate,competitiveProducts,luxury,onVacation,characteristic,hotRecommend,referencesPrice,specialPreference,imgShow,imgName,imgUrl,newProduct,overflow,season,priority from Tab_TouristTrack
where (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=1
or 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=1
or 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=1
or specialPreference=1 or hotRecommend=1 or overflow=1 or season=1)

 

使用括号的目的就是将一小段sql子句作为一个整体来使用.

简单的说就是在满足前面所有子句的情况下还要满足(priority=0 or priority is null)返回为ture的数据.

 

 

  private void PriorityBinds()
    {
        gvPriority.DataKeyNames = new String[] { "id" };
        gvPriority.DataSource = tts.PriorityQuery(ddlType.SelectedValue,txtBeginDate.Text,txtEndDate.Text
            ,txtTouristTrackName.Text,rblPriority.SelectedValue);
        gvPriority.DataBind();
    }

 public DataTable PriorityQuery(String MenuType, String beginDate, String endDate, String routeName, String priority)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(" select id,routeName,routeCharacteristic,routeIntroductions,costDetail,participate,click,routeCategory,dineMenu,weather,isEnable,addPerson,addDate,competitiveProducts,luxury,onVacation,characteristic,hotRecommend,referencesPrice,specialPreference,imgShow,imgName,imgUrl,newProduct,overflow,season,priority ");
            strSql.Append(" from Tab_TouristTrack ");
            strSql.Append(" where (addDate>=@beginDate or @beginDate='') and (addDate<=dateadd(dd,1,@endDate) or @endDate='')");
            strSql.Append(" and (routeName like @routeName or @routeName='')");

            if (MenuType != "all")
            {
                    strSql.Append(" and "+MenuType + "=1");
            }
            else
            {
                strSql.Append(" and (newProduct=1 or competitiveProducts=1 or luxury=1 or onVacation=1 or characteristic=1");
                strSql.Append(" or specialPreference=1 or hotRecommend=1 or overflow=1 or season=1)");
            }

            if (priority!="all")
            {
                if (priority=="1")
                {
                    strSql.Append(" and priority=1");
                }
                else
                {
                    strSql.Append(" and (priority=0 or priority is null)");
                }
            }

            strSql.Append(" order by priority desc");

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@beginDate",beginDate),
                new SqlParameter("@endDate",endDate),
                new SqlParameter("@routeName","%"+routeName+"%")
            };

            return SQLLinkDatabase.Query(strSql.ToString(),param).Tables[0];
        }