我有一个网上商店DATABASE
,其中有4个TABLES
==>类别,客户,项目,订单。TABLE
类别==>
+------------+-------------+--------+
| categoryid | title | status |
+------------+-------------+--------+
| 1 | Electronics | 1 |
| 2 | Books | 1 |
| 3 | Cloths | 1 |
+------------+-------------+--------+
TABLE
项==>+--------+------------+-----------------------------------------+-------+
| itemid | categoryid | name | price |
+--------+------------+-----------------------------------------+-------+
| 1 | 1 | Android Mobile Phone | 250 |
| 2 | 1 | i7 processor 8GB RAM Laptop | 1000 |
| 3 | 2 | How to train your cat | 25 |
| 4 | 2 | Healthy dog food recipes | 19 |
| 5 | 2 | Learn how to meditate fro mental health | 30 |
| 6 | 3 | Beaautiful Black T-Shirts | 99 |
| 7 | 3 | Blue Colored Jeans | 150 |
+--------+------------+-----------------------------------------+-------+
在这里,我要从类别标题不是“书籍和布料”的项目中过滤出名称
我正在编写以下查询以过滤出图书
SELECT name
FROM items
WHERE NOT EXISTS
( SELECT *
FROM categories
WHERE categories.categoryid = items.categoryid
AND categories.title = 'Books');
结果->
+-----------------------------+
| name |
+-----------------------------+
| Android Mobile Phone |
| i7 processor 8GB RAM Laptop |
| Beaautiful Black T-Shirts |
| Blue Colored Jeans |
+-----------------------------+
现在我也想过滤出Cloth,这样结果就是->
+-----------------------------+
| name |
+-----------------------------+
| Android Mobile Phone |
| i7 processor 8GB RAM Laptop |
+-----------------------------+
怎么做?
最佳答案
您可以使用NOT EXISTS
来做到这一点:
SELECT i.name
FROM items i
WHERE NOT EXISTS (
SELECT 1 FROM categories
WHERE categoryid = i.categoryid AND title IN ('Books', 'Cloths')
);
或使用
NOT IN
:SELECT name
FROM items
WHERE categoryid NOT IN (
SELECT categoryid
FROM categories
WHERE title IN ('Books', 'Cloths')
);
请参见demo。
结果:
> | name |
> | :-------------------------- |
> | Android Mobile Phone |
> | i7 processor 8GB RAM Laptop |
关于mysql - 无法在同时满足两个条件的sql查询中过滤结果,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/58713591/