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

Question

下面是现有的 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%'

结果将如下所示：

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

我想要做的是修改查询，以排除客户为“FTS%”和“EMC%”且batchtype =“PC”的记录。正如您在结果集中看到的，有一些记录，其中客户类似于 FTS% 且批次类型 = 'PA'。我想将这些记录保留在结果中。我将不胜感激任何提供的想法。

Answer 1

您的查询包含上下字符串比较目标的混合。据我所知，SQL Server 默认情况下不区分大小写；这有可能是导致您查询失败的原因吗？根据此答案检查排序规则。

编辑：根据您更新的问题，您不能只使用前面使用 NOT 的 AND 子句吗？
换句话说，添加一个“AND not (x)”子句，其中“x”是定义要排除的记录的条件？您需要嵌套客户测试，因为它是一个 OR。
例如：

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

作为旁注，我认为 LIKE 子句可能意味着某些表扫描效率低下（但是 并非所有）情况，因此，如果此查询将用于性能敏感的角色，您可能需要检查查询计划，并且优化表格以适应。

Answer 2

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

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

Answer 3

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

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'

Answer 4

将此条件添加到 WHERE 子句中：

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

假设您的其他结果都正常，并且您只想过滤掉这些结果，则整个查询将

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')

希望适合您。

Answer 5

在构建复杂的 where 子句时，最好使用括号来保持一切正常。此外，当使用多个 NOT LIKE 语句时，您必须使用 OR 将所有 NOT LIKE 条件组合在一起，并将它们包装在单独的 >并且像这样的条件...

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