mysql - Mysql 获取包含匹配另一个条件的所有数据

Question

在使用某些包容性标准获取所有数据时，例如当列名等于“蓝色”或“黄色”且“颜色值”不等于“100”时

> mysql> select * from Colors where colorname is not null;
+-----------+----------+
| colorname | colorval |
+-----------+----------+
| blue      | 100      |
| blue      | 200      |
| blue      | 300      |
| red       | 200      |
| red       | 300      |
| red       | 100      |
| red       | 400      |
| yellow    | 100      |
| yellow    | 200      |
| yellow    | 300      |
+-----------+----------+

我希望结果是

> 
+-----------+----------+
| colorname | colorval |
+-----------+----------+
| blue      | 200      |
| blue      | 300      |
| red       | 200      |
| red       | 300      |
| red       | 100      |
| red       | 400      |
| yellow    | 200      |
| yellow    | 300      |
+-----------+----------+

标签： mysqlsqlwhere-clause

Answer 1

这是需要的条件：

select * from colors 
where colorname is not null and (colorval <> 100 or colorname not in ('blue', 'yellow')) 

您需要的是 colorval 不等于 100 并且 colorname 不是同一行中的“blue”、“yellow”中的任何一个。
见演示。
结果：

| colorname | colorval |
| --------- | -------- |
| blue      | 200      |
| blue      | 300      |
| red       | 200      |
| red       | 300      |
| red       | 100      |
| red       | 400      |
| yellow    | 200      |
| yellow    | 300      |

如果你只有这 3 种颜色，你可以这样简化：

select * from colors 
where colorname is not null and (colorval <> 100 or colorname = 'red')