根据sql server中的多个条件过滤表数据 [英] Filtering table data based on multiple conditions from sql server

问题描述

我有6个列表框和三个日期文本框，我用它来对数据库表应用条件过滤。我需要的是，如果我只选择一个文本框或列表框值，或多个列表框值和多个日期值，我应该能够根据前端选择的过滤条件检索数据。我应该跳过我没有选择的日期或列表框值。



我有以下查询但它不起作用。我该如何编写查询？

I have 6 listboxes and three date textboxes which I'm using to apply conditional filtering on a database table. What I need is if I select only a single textbox or a listbox value, or multiple listbox values and multiple date values I should be able to retrieve data based on only selected filter conditions from front end. The date or listbox values that I don't select should be skipped.

I have the following query but its not working.How should i write the query?

SELECT (MPMST.partyname + '' + ',' + '' + MPMST.contactperson) AS NAME
      ,(MPMST.email + '' + ',' + '' + MPMST.phone) AS ContactDetail
      ,MPMST.address
      ,(MPMST.city + '' + ',' + '' + MPMST.area) AS city_area
      ,MPMST.Already_Computer
      ,MPMST.software
      ,visit_time
      ,visit_purpose
      ,interaction
FROM    MPMST
INNER
JOIN    MCall   ON  MPMST.Partycode = MCall.Partycode
WHERE  MCall.calltype IN ('SUP001')
AND MPMST.area IN ('pavai')
AND MPMST.city  IN ('mumbai')
AND MCall.Date >= CONVERT(DATETIME ,'01/01/2014' ,103)
AND MCall.Date <= CONVERT(DATETIME ,'31/01/2014' ,103) MCall.status = ''
AND MPMST.software = ''
AND MPMST.Software <> ''
AND MPMST.City IS NOT NULL
AND MPMST.Area IS NOT NULL

我以前的查询就好像

my previous query was like

WHERE MCall.calltype IN ('SUP001') OR MPMST.area IN ('pavai') OR MPMST.city IN ('mumbai') AND OR MCall.status = '' OR MPMST.software = '' AND MPMST.Software <> '' AND MPMST.City IS NOT NULL AND MPMST.Area IS NOT NULL

..这个查询只给了我满足第一个条件的记录ie.Sup001'

..this query gave me only the records satisfying the first condition i.e..'Sup001'

推荐答案

略微修改你的条件如下：

WHERE （cond1 或 cond2）

和（cond3 或 cond4）

和（cond5 或 cond6）



如果有任何问题请告诉我们

slightly modify your where condition like :
WHERE (cond1 or cond2)
and (cond3 or cond4)
and (cond5 or cond6)

if there would be any issue plz do let me know

WHERE MCall.calltype IN（'SUP001' ）或MPMST.area IN（'pavai'）或MPMST.city IN（'mumbai'）AND OR MCall.status =''或MPMST.software =''和MPMST.Software<> ''和MPMST.City不是空，MPMST.Area不是空的



有人给你的答案是对的。 '不工作'没有帮助。问题非常简单 - SQL有一个操作顺序（即，它应用的顺序），而不是你所假设的。使用'OR'关键字时要非常小心。确保所有条件都在括号中，以便哪些组是显式的。



WHERE

（MCall.calltype IN（ 'SUP001'）或

MPMST.area IN（'pavai'）或

MPMST.city IN（'mumbai'）

AND

（MCall.status =''或MPMST.software =''）

AND MPMST.Software<> ''和MPMST.City不是空，MPMST.Area不是空的



与


$ b非常不同$ b

WHERE

MCall.calltype IN（'SUP001'）或

MPMST.area IN（'pavai'）或
（MPMST.city IN（'mumbai'）AND（MCall.status =''或MPMST.software =''

AND MPMST.Software<>''和MPMST .City IS NOT NULL且MPMST.Area不为空）



例如。

WHERE MCall.calltype IN ('SUP001') OR MPMST.area IN ('pavai') OR MPMST.city IN ('mumbai') AND OR MCall.status = '' OR MPMST.software = '' AND MPMST.Software <> '' AND MPMST.City IS NOT NULL AND MPMST.Area IS NOT NULL

The answer someone gave you is right. 'not working' does not help. The issue is very simple - SQL has an order of operations ( that is, order in which it applies things ) and it's not what you're assuming. Be VERY careful with the 'OR' keyword. Make sure all your conditions are in brackets so that which groups are to be ORed is explicit.

WHERE
(MCall.calltype IN ('SUP001') OR
MPMST.area IN ('pavai') OR
MPMST.city IN ('mumbai') )
AND
(MCall.status = '' OR MPMST.software = '')
AND MPMST.Software <> '' AND MPMST.City IS NOT NULL AND MPMST.Area IS NOT NULL

is very different to


WHERE
MCall.calltype IN ('SUP001') OR
MPMST.area IN ('pavai') OR
(MPMST.city IN ('mumbai') AND (MCall.status = '' OR MPMST.software = ''
AND MPMST.Software <> '' AND MPMST.City IS NOT NULL AND MPMST.Area IS NOT NULL)

For example.

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