MySQL 查询要过滤的多列？

Question

假设我只有 1 个表，其中包含多个列。比方说:

Fruits
--------------------------------------
id  | name        | country
--------------------------------------
1   | Banana      | china
2   | Orange      | japan
3   | Apple       | japan
4   | Apple       | china
5   | Banana      | usa
6   | Orange      | china

然后简单查询到SELECT所有水果id < 6 .
它是:

SELECT * FROM fruits WHERE id < 6

然后它将返回:

--------------------------------------
id  | name        | country
--------------------------------------
1   | Banana      | china
2   | Orange      | japan
3   | Apple       | japan
4   | Apple       | china
5   | Banana      | usa

然后通过附加上面的查询，我怎样才能继续排除更多的东西。
例如:

Do NOT want to see, any "APPLE" from "CHINA".

所以最后的结果一定是:

--------------------------------------
id  | name        | country
--------------------------------------
1   | Banana      | china
2   | Orange      | japan
3   | Apple       | japan
5   | Banana      | usa

这又是without有if, name='Apple' AND country='china'的行一起!。

• 因此，继续第一个查询，我如何添加附加的“AND 条件对”以获得此结果？

我会说:

SELECT * FROM fruits WHERE id < 6 ... AND DON'T RETURN this condition where name='Apple' and country='china' TOGETHER

Answer 1

尝试:

SELECT * 
FROM fruits 
WHERE (id < 6)
AND NOT (name = 'Apple' AND country = 'china')

或者:

SELECT * 
FROM fruits 
WHERE (id < 6)
AND id NOT IN (SELECT id 
               FROM fruits
               WHERE name = 'Apple'
               AND country = 'china')