I have a database with Name Database1 and Table named table1.The columns in the table are Client Name and Country.I want to search for clients based on a condition using LIKE and want to Exclude in results the Clients from NOT LIKE (NOT IN) Countries.
我有一个名为Database1的数据库和名为table1的表。表中的列是Client Name和Country.I想要根据使用LIKE的条件搜索客户端,并希望在结果中排除客户端从NOT LIKE(NOT IN)国家。
Client Name,Country
Sandeep,India
Mandeep,USA
John,Japan
Vinay,China
Amit,USA
etc...
I tried below stated query.It does give out results but does not filter or exclude the Not Like countries, rather displays all countries.
我尝试了下面陈述的查询。它确实给出结果,但不过滤或排除不喜欢的国家,而是显示所有国家。
SELECT CLIENT NAME,
COUNTRY
FROM DATABASE1.TABLE1
WHERE (CLIENT NAME LIKE 'VINAY')
OR (CLIENT NAME LIKE 'AMIT')
AND COUNTRY NOT IN ('INDIA',
'JAPAN')
I have also tried with NOT LIKE
:
我也尝试过不喜欢:
SELECT CLIENT NAME,
COUNTRY
FROM DATABASE1.TABLE1
WHERE (CLIENT NAME LIKE 'VINAY')
OR (CLIENT NAME LIKE 'AMIT')
AND (COUNTRY NAME NOT LIKE 'INDIA')
AND (COUNTRY NAME NOT LIKE 'JAPAN')
Both of above gave results But without filtering-out/excluding India and China in results.Results are appearing for all countries in-spite of using Not LIKE / NOT IN
以上两个都给出了结果但没有过滤掉/排除印度和中国的结果。尽管使用了不喜欢/不在,所有国家都出现了结果
Any Solution or what is my mistake ?
任何解决方案或我的错误是什么?
2 个解决方案
#1
0
Change in both queires:
两个队列的变化:
Query 1:
查询1:
SELECT CLIENT NAME,
COUNTRY
FROM DATABASE1.TABLE1
WHERE (CLIENT NAME LIKE 'VINAY' OR CLIENT NAME LIKE 'AMIT')
AND COUNTRY NOT IN ('INDIA', 'JAPAN')
Query 2:
查询2:
SELECT CLIENT NAME,
COUNTRY
FROM DATABASE1.TABLE1
WHERE (CLIENT NAME LIKE 'VINAY' OR CLIENT NAME LIKE 'AMIT')
AND COUNTRY NAME NOT LIKE 'INDIA' AND COUNTRY NAME NOT LIKE 'JAPAN'
#2
0
AND
has higher precedence than OR
, so your first filter is equivalent to:
AND的优先级高于OR,因此您的第一个过滤器相当于:
(CLIENT NAME LIKE 'VINAY') OR (
(CLIENT NAME LIKE 'AMIT') AND COUNTRY NOT IN ('INDIA','JAPAN')
)
Similarly, your second filter is equivalent to:
同样,您的第二个过滤器相当于:
(CLIENT NAME LIKE 'VINAY') OR (
(CLIENT NAME LIKE 'AMIT') AND
(COUNTRY NAME NOT LIKE 'INDIA') AND
(COUNTRY NAME NOT LIKE 'JAPAN')
)
To specify alternative precedence, you should include explicit parentheses:
要指定备用优先级,您应该包括显式括号:
(CLIENT NAME LIKE 'VINAY' OR CLIENT NAME LIKE 'AMIT')
AND COUNTRY NOT IN ('INDIA','JAPAN')
(Note that wrapping individual terms in their own parenthesis contributes nothing, so I have removed them).
(请注意,在自己的括号中包含单个术语不会产生任何影响,因此我已将其删除)。
Note also that your expression can be abbreviated using IN()
for the OR
terms:
另请注意,对于OR项,您的表达式可以使用IN()缩写:
CLIENT NAME IN ('VINAY','AMIT') AND COUNTRY NOT IN ('INDIA','JAPAN')
#1
0
Change in both queires:
两个队列的变化:
Query 1:
查询1:
SELECT CLIENT NAME,
COUNTRY
FROM DATABASE1.TABLE1
WHERE (CLIENT NAME LIKE 'VINAY' OR CLIENT NAME LIKE 'AMIT')
AND COUNTRY NOT IN ('INDIA', 'JAPAN')
Query 2:
查询2:
SELECT CLIENT NAME,
COUNTRY
FROM DATABASE1.TABLE1
WHERE (CLIENT NAME LIKE 'VINAY' OR CLIENT NAME LIKE 'AMIT')
AND COUNTRY NAME NOT LIKE 'INDIA' AND COUNTRY NAME NOT LIKE 'JAPAN'
#2
0
AND
has higher precedence than OR
, so your first filter is equivalent to:
AND的优先级高于OR,因此您的第一个过滤器相当于:
(CLIENT NAME LIKE 'VINAY') OR (
(CLIENT NAME LIKE 'AMIT') AND COUNTRY NOT IN ('INDIA','JAPAN')
)
Similarly, your second filter is equivalent to:
同样,您的第二个过滤器相当于:
(CLIENT NAME LIKE 'VINAY') OR (
(CLIENT NAME LIKE 'AMIT') AND
(COUNTRY NAME NOT LIKE 'INDIA') AND
(COUNTRY NAME NOT LIKE 'JAPAN')
)
To specify alternative precedence, you should include explicit parentheses:
要指定备用优先级,您应该包括显式括号:
(CLIENT NAME LIKE 'VINAY' OR CLIENT NAME LIKE 'AMIT')
AND COUNTRY NOT IN ('INDIA','JAPAN')
(Note that wrapping individual terms in their own parenthesis contributes nothing, so I have removed them).
(请注意,在自己的括号中包含单个术语不会产生任何影响,因此我已将其删除)。
Note also that your expression can be abbreviated using IN()
for the OR
terms:
另请注意,对于OR项,您的表达式可以使用IN()缩写:
CLIENT NAME IN ('VINAY','AMIT') AND COUNTRY NOT IN ('INDIA','JAPAN')