给定这两个表:
产品
product_id name
1 shampoo
2 hairbrush
products_to_categories
id product_id category_id
0 1 100
1 1 200
2 2 100
我想编写 SQL,它将为我提供类别 100 和类别 200 中的所有产品(即 id 为 1 的产品(。
(我们可以假设存在类别表,products_to_categories是一个链接表(。
select p.*, pc.* from products as p
inner join products_to_categories as pc
where pc.category_id = 100 and pc.category_id = 200;
显然不起作用,因为没有行同时具有这两个值。
我想过运行两个选择,然后运行一个在两集中只找到product_ids的操作 - 但 UNION 是附加的而不是减法的。
这有效,但重量不轻;
select products.* from products where products.product_id IN (
select p.product_id from products as p
inner join products_to_categories as pc ON pc.product_id = p.product_id AND pc.category_id = 100
)
AND
products.product_id IN (
select p.product_id from products as p
inner join products_to_categories as pc ON pc.product_id = p.product_id AND pc.category_id = 200
) ;
一定有更好的方法可以做到这一点吗?
谢谢
group by
和having
:
select p.*
from products p inner join
products_to_categories pc
on p.product_id = pc.product_id
where pc.category_id in (100, 200);
group by p.product_id
having count(distinct category_id) = 2;
注意:这使用 ANSI SQL 功能,该功能允许您按表的主键分组并引用其余列。 并非所有数据库都支持此功能。 因此,在许多数据库中,您需要在group by
中单独列出产品字段。