SQL 使用多个相似条件选择查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7758266/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
select query using multiple like conditions
提问by user992322
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 ,445.41 89.41
4544785 PCR WAP1001 8 2011 12:00:00 AM NCO DP ,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'。我想在结果中保留这些记录。我将不胜感激提供的任何想法。
采纳答案by Geoff
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.:
编辑：根据您更新的问题，您不能只使用在前面使用 NOT 的 AND 子句吗？换句话说，添加一个“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 子句在某些（但不是全部）情况下可能意味着表扫描效率低下，因此如果此查询将用于对性能敏感的角色，您可能需要检查查询计划并优化适合的桌子。
回答by Anki007
$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 plazaor complex.
这将返回表中的所有构建器属性名称，这些名称以plaza或结尾complex。
回答by Bala
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'
回答by tedders
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
假设您的其他结果没问题，而您只想过滤掉这些结果，则整个查询将是
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.
希望这对你有用。
回答by Michael Riley - AKA Gunny
When building complex where clauses it is a good idea to use parenthesis to keep everything straight. Also when using multiple NOT LIKEstatements you have to combine all of the NOT LIKEconditions together using ORsand wrap them inside of a separate ANDcondition like this...
在构建复杂的 where 子句时，最好使用括号来保持一切正常。此外，当使用多个NOT LIKE语句时，您必须使用OR将所有NOT LIKE条件组合在一起，并将它们包装在一个单独的AND条件中，如下所示......
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
