使用多个条件的数据库过滤

本文介绍了使用多个条件的数据库过滤的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我有一个文本框和3个下拉框，其中包含可在每个下拉列表中选择的项目。我希望能够实现两种过滤方案。 1.在文本框中键入值后获取过滤结果，然后在接下来的两个文本框中选择值，而不显示最后一个。 2.在文本框中键入值后获取过滤，然后在其他三个下拉框中选择值以过滤掉结果。我下面的代码只能一次生成一个结果如果我将和更改为或，将或更改为和。任何人都可以帮助修改或新查询以实现这两种情况吗？ 我尝试过： CREATE proc spSearchProduct @ searchWord1OnMasterPage nvarChar （ 50 ）， @ searchWord2OnMasterPage nvarChar （ 50 ）， @ searchWord3OnMasterPage nvarChar （ 50 ）， @ searchWord4OnMasterPage nvarChar （ 50 ） as 开始 选择 Product.Name，Price，Seller，ProductStreetNo.StrNo，ProductStreet.StreetName from 产品 INNER JOIN ProductStreetNo ON Product.StreetNoID = ProductStreetNo.IDStreetNo INNER JOIN ProductStreet ON Product.StreetID = ProductStreet.IDStreet INNER JOIN ProductState ON StateID = ProductState.IDState INNER JOIN ProductCity ON CityID = ProductCity.IDCity 其中 产品。名称喜欢 ' ％' + @ searchWord1OnMasterPage + ' ％' 和 ProductState.StateName 赞 ' ％' + @ searchWord2OnMasterPage + ' ％' 和 （（ProductCity.CityName 赞 ' ％' + @ searchWord3OnMasterPage + ' ％'）或 （ProductStreet.StreetName 赞 ' ％' + @ searchWord4OnMasterPage + ' ％'）或 （ProductStreet.StreetName null ））和 （（ProductCity.CityName 赞 ' ％' + @ searchWord3OnMasterPage + ' ％'）或 （ProductStreet.StreetName 赞 ' ％' + @ searchWord4OnMasterPage + ' ％'） 或 （ProductStreet.StreetNam e null ）） 结束 解决方案 更改查询 **请仔细查看 从产品中选择Product.Name，Price，Seller，ProductStreetNo.StrNo，ProductStreet.StreetName INNER JOIN ProductStreetNo ON Product.StreetNoID = ProductStreetNo.IDStreetNo INNER JOIN ProductStreet ON Product.StreetID = ProductStreet.IDStreet INNER JOIN ProductState ON StateID = ProductState.IDState INNER JOIN ProductCity ON CityID = ProductCity.IDCity where （ （@ searchWord1OnMasterPage为空） 或 （Product.Name喜欢'％'+ @ searchWord1OnMasterPage +'％'） b $ b） 和 （ （@ searchWord2OnMasterPage I S NULL） 或 （ProductState.StateName喜欢'％'+ @ searchWord2OnMasterPage +'％'） ） 和 （ （@ searchWord3OnMasterPage为空） 或 （ProductCity.CityName赞' ％'+ @ searchWord3OnMasterPage +'％'） ） 和 （（@ searchWord4OnMasterPage IS NULL） 或 （ProductStreet.StreetName喜欢'％'+ @ searchWord4OnMasterPage +'％'） ） Product.Name赞'％'+ @ searchWord1OnMasterPage +'％'和 ProductState.StateName喜欢'％'+ @ searchWord2OnMasterPage +'％'和 （ProductCity.CityName喜欢'％'+ @ searchWord3OnMasterPage +'％'）和 （ （ProductStreet.StreetName喜欢'％'+ @ searchWord4OnMasterPage +'％'）或 （@ searchWord4OnMasterPage ='Select Street'）） I have a textbox and 3 dropdown boxes with an item that could be selected in each of the dropdowns. There are two filtering scenarios I want to be able to achieve.1.Getting a filtering result after typing a value in the textbox and select values in the next two textboxes leaving out the last one.2.Getting a filtering after typing a value in the textbox and select values in the other three dropdown boxes to filter out the result. My code below can only produce each of the results one at a time If I change the "and" to "or" and the "or"s to "and"s. Can anyone help with modification or new query to be able to achieve the two scenarios?What I have tried:CREATE proc spSearchProduct@searchWord1OnMasterPage nvarChar(50),@searchWord2OnMasterPage nvarChar (50),@searchWord3OnMasterPage nvarChar (50),@searchWord4OnMasterPage nvarChar (50)asBegin Select Product.Name,Price,Seller,ProductStreetNo.StrNo,ProductStreet.StreetName from Product INNER JOIN ProductStreetNo ON Product.StreetNoID = ProductStreetNo.IDStreetNo INNER JOIN ProductStreet ON Product.StreetID = ProductStreet.IDStreet INNER JOIN ProductState ON StateID=ProductState.IDState INNER JOIN ProductCity ON CityID=ProductCity.IDCity whereProduct.Name Like '%' + @searchWord1OnMasterPage + '%' and ProductState.StateName Like '%' + @searchWord2OnMasterPage + '%' and ((ProductCity.CityName Like '%' + @searchWord3OnMasterPage + '%' )or (ProductStreet.StreetName Like '%' + @searchWord4OnMasterPage + '%') or (ProductStreet.StreetName is null)) and ((ProductCity.CityName Like '%' + @searchWord3OnMasterPage + '%' )or (ProductStreet.StreetName Like '%' + @searchWord4OnMasterPage + '%') or (ProductStreet.StreetName is null))End 解决方案 Change In Query** Please See Carefully Select Product.Name,Price,Seller,ProductStreetNo.StrNo,ProductStreet.StreetName from Product INNER JOIN ProductStreetNo ON Product.StreetNoID = ProductStreetNo.IDStreetNo INNER JOIN ProductStreet ON Product.StreetID = ProductStreet.IDStreet INNER JOIN ProductState ON StateID=ProductState.IDState INNER JOIN ProductCity ON CityID=ProductCity.IDCity where((@searchWord1OnMasterPage IS NULL)OR(Product.Name Like '%' + @searchWord1OnMasterPage + '%' ))and((@searchWord2OnMasterPage IS NULL)OR(ProductState.StateName Like '%' + @searchWord2OnMasterPage + '%'))and ((@searchWord3OnMasterPage IS NULL)OR(ProductCity.CityName Like '%' + @searchWord3OnMasterPage + '%' ))and((@searchWord4OnMasterPage IS NULL)OR(ProductStreet.StreetName Like '%' + @searchWord4OnMasterPage + '%'))Product.Name Like '%' + @searchWord1OnMasterPage + '%' andProductState.StateName Like '%' + @searchWord2OnMasterPage + '%' and(ProductCity.CityName Like '%' + @searchWord3OnMasterPage + '%' )and((ProductStreet.StreetName Like '%' + @searchWord4OnMasterPage + '%') or(@searchWord4OnMasterPage = 'Select Street')) 这篇关于使用多个条件的数据库过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！