mysql - 选择没有特定值(value)的项目

Question

我有产品表——每个表都有几个类别；每个类别都有一个主栏。 (我还有一个类别表和一个产品类别表。)

如果 main = 1 -> 它是主要类别

我想选择所有没有主类别的产品(与该产品相关的所有类别都没有 main = 1 列)。

这是选择所有主要类别的方法:

SELECT *
  FROM categories
 WHERE id IN (SELECT DISTINCT category_id
                FROM `product_categories`
               WHERE main = 1);

如何找到没有主要类别的产品？

Answer 1

正如您提到的，我想选择所有没有主类别的产品(与该产品相关的所有类别都没有 main = 1 列)。 em>

这就是你想要的

select * from products where prod_id not in (select distinct prod_id from categories where main =1);

我试过了。

mysql> desc products;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| prod_id | int(11)      | YES  |     | NULL    |       |
| name    | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> desc categories;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| prod_id | int(11) | YES  |     | NULL    |       |
| cat_id  | int(11) | YES  |     | NULL    |       |
| main    | int(11) | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from products;
+---------+-------+
| prod_id | name  |
+---------+-------+
|       1 | prod1 |
|       2 | prod2 |
|       3 | prod3 |
|       4 | prod4 |
+---------+-------+
4 rows in set (0.00 sec)

mysql> select * from categories;
+---------+--------+------+
| prod_id | cat_id | main |
+---------+--------+------+
|       1 |      1 |    1 |
|       1 |      2 |    2 |
|       2 |      1 |    2 |
|       2 |      1 |    3 |
|       3 |      2 |    1 |
|       3 |      4 |    2 |
+---------+--------+------+
6 rows in set (0.00 sec)

mysql> select * from products where prod_id not in (select distinct prod_id from categories where main =1);
+---------+-------+
| prod_id | name  |
+---------+-------+
|       2 | prod2 |
|       4 | prod4 |
+---------+-------+
2 rows in set (0.00 sec)