我有一个网上商店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/

10-12 14:17
查看更多