使用多个条件的数据库过滤 [英] Database filtering using multiple conditions

问题描述

我有一个文本框和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  + < span class =code-string>' ％'）或 
 （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 +'％'）< br $> 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)
as
Begin
    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 

Product.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 + '%' and

ProductState.StateName Like '%' + @searchWord2OnMasterPage + '%' and

(ProductCity.CityName Like '%' + @searchWord3OnMasterPage + '%' )and


((ProductStreet.StreetName Like '%' + @searchWord4OnMasterPage + '%') or
(@searchWord4OnMasterPage = 'Select Street'))

这篇关于使用多个条件的数据库过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！