使用多个相似条件选择查询

Below is an existing ms sql server 2008 report query.

下面是一个现有的ms sql server 2008报表查询。

SELECT
    number, batchtype, customer, systemmonth, systemyear, entered, comment, totalpaid
FROM
    payhistory LEFT OUTER JOIN agency ON
        payhistory.SendingID = agency.agencyid      
WHERE
    payhistory.batchtype LIKE 'p%' AND
    payhistory.entered >= '2011-08-01 00:00:00.00' AND
    payhistory.entered <  '2011-08-15 00:00:00.00' AND
    payhistory.systemmonth = 8 AND
    payhistory.systemyear = 2011 AND
    payhistory.comment NOT LIKE 'Elit%'

Results will look like this:

结果如下:

number  batchtype   customer    systemmonth systemyear  entered     comment         totalpaid
6255756 PC      EMC1106     8       2011        12:00:00 AM DP From - NO CASH       33
5575317 PA      ERS002      8       2011        12:00:00 AM MO-0051381526 7/31      20
6227031 PA      FTS1104     8       2011        12:00:00 AM MO-10422682168 7/30     25
6232589 PC      FTS1104     8       2011        12:00:00 AM DP From - NO CASH       103
2548281 PC      WAP1001     8       2011        12:00:00 AM NCO DP $1,445.41        89.41
4544785 PCR     WAP1001     8       2011        12:00:00 AM NCO DP $1,445.41        39

What I am trying to do is modify the query that will exclude records where the customer is like 'FTS%' and 'EMC%' and batchtype = 'PC'. As you can see in the result set there are records where customer is like FTS% and batchtype = 'PA'. I would like to keep these records in the results. I would appreciate any ideas offered.

我所要做的是修改查询，该查询将排除客户如“FTS%”和“EMC%”和batchtype =“PC”的记录。正如您在结果集中所看到的，在这些记录中，客户如FTS%和batchtype = 'PA'。我想把这些记录保存在结果里。如有任何意见，我将不胜感激。

5 个解决方案

#1

Your query contains a mix of upper and lower string comparison targets. As far as I'm aware, SQL Server is not by default case-sensitive; is it possible this is what is tripping your query up? Check collation per this answer.

您的查询包含上下字符串比较目标的混合。据我所知，SQL Server默认不区分大小写;这可能是导致查询出错的原因吗?检查每个答案的排序。

EDIT: Based on your updated question, can you not just use an AND clause that uses a NOT on the front? In other words, add a 'AND not (x)' clause, where 'x' is the conditions that define the records you want to exclude? You'd need to nest the customer test, because it's an OR. e.g.:

编辑:基于你更新的问题，你能不能仅仅使用一个不在前面的和子句?换句话说，添加一个'AND not (x)'子句，其中'x'是定义您想要排除的记录的条件?您需要嵌套客户测试，因为它是OR。例如:

... payhistory.comment NOT LIKE 'Elit%'
AND not ((customer like 'FTS%' or customer like 'EMC%') AND batchtype = 'PC')

As a side note, I believe that a LIKE clause may imply an inefficient table scan in some (but not all) cases, so if this query will be used in a performance-sensitive role you may want to check the query plan, and optimise the table to suit.

作为补充说明，我认为LIKE子句可能意味着在某些(但不是所有)情况下执行低效的表扫描，因此，如果这个查询将在性能敏感的角色中使用，您可能希望检查查询计划，并优化表以适应这种情况。

#2

$sql="select * from builder_property where builder_pro_name LIKE '%%' OR builder_pro_name LIKE '%za%' AND status='Active'";

This will return all the builder property name in table that will ends name like plaza or complex.

这将返回表中以plaza或complex等名称结尾的所有builder属性名。

#3

It can be because your sever might be case sensitive. In that case, below query would work.

这可能是因为您的服务器可能是区分大小写的。在这种情况下，下面的查询可以工作。

SELECT
table1.number, table1.btype, table1.cust, table1.comment, table2.ACode
FROM
table1 LEFT OUTER JOIN table2 ON table1.1ID = table2.2ID
WHERE
lower(table1.btype) LIKE 'p%' AND
lower(table1.comment) NOT LIKE 'yyy%' AND
lower(table1.cust) NOT LIKE 'abc%' AND
lower(table1.cust) NOT LIKE 'xyz%' AND
lower(table1.btype) <> 'pc'

#4

Add this condition to the WHERE clause:

将此条件添加到WHERE子句:

NOT((customer LIKE 'FTS%' OR customer LIKE 'EMC%') AND batchtype='PC')

Assuming your other results are OK and you just want to filter those out, the whole query would be

假设您的其他结果是OK的，并且您只想过滤掉它们，那么整个查询将是

SELECT
    number, batchtype, customer, systemmonth, systemyear, entered, comment, totalpaid
FROM
    payhistory 
    LEFT OUTER JOIN agency ON
        payhistory.SendingID = agency.agencyid      
WHERE
    payhistory.batchtype LIKE 'p%' AND
    payhistory.entered >= '2011-08-01 00:00:00.00' AND
    payhistory.entered <  '2011-08-15 00:00:00.00' AND
    payhistory.systemmonth = 8 AND
    payhistory.systemyear = 2011 AND
    payhistory.comment NOT LIKE 'Elit%' AND
    NOT((payhistory.customer LIKE 'FTS%' OR payhistory.customer LIKE 'EMC%') AND payhistory.batchtype='PC')

Hope that works for you.

希望对你有用。

#5

When building complex where clauses it is a good idea to use parenthesis to keep everything straight. Also when using multiple NOT LIKE statements you have to combine all of the NOT LIKE conditions together using ORs and wrap them inside of a separate AND condition like this...

在构建复杂的从句时，最好使用括号来保持一切正常。同样，当使用多个不喜欢的语句时，你必须使用ORs将所有不喜欢的条件组合在一起，并将它们封装在一个单独的、类似这样的条件中……


WHERE     
    (payhistory.batchtype LIKE 'p%')
AND (payhistory.entered >= '2011-08-01 00:00:00.00')
AND (payhistory.entered <  '2011-08-15 00:00:00.00')
AND (payhistory.systemmonth = 8 )
AND (payhistory.systemyear = 2011)
AND ( // BEGIN NOT LIKE CODE

    (payhistory.comment NOT LIKE 'Elit%') 

OR  (
    (payhistory.customer NOT LIKE 'EMC%') AND 
    (payhistory.batchtype = 'PC')
    ) 

OR  (
    (payhistory.customer NOT LIKE 'FTS%') AND 
    (payhistory.batchtype = 'PC')
    )

    ) //END NOT LIKE CODE

#1