Mysql与其他2个表重复查询结果，IN操作符

Question

我有3个主表:产品商店,类别和

• product关联多对多与storesvia tableproduct_store

• 产品关联多对多与类别通过表product_category

如何查询所有产品，属于一个商店(store_id: 1)，属于一个或多个类别(与category_id在[4,5,6]?我使用下面的语法，但结果有重复的产品记录(例如，产品在类别5和6，将显示2次)。

select 
"products"."name" 
from 
"products" 
inner join "product_categories" as "categories_join" on "categories_join"."product_id" = "products"."id" 
inner join "categories" on "categories_join"."category_id" = "categories"."id" 
inner join "product_stores" as "stores_join" on "stores_join"."product_id" = "products"."id" 
inner join "stores" on "stores_join"."store_id" = "stores"."id" 
where 
"categories"."id" in ( 4,5,6,7 ) 
and "stores"."id" = 1

Answer 1

可以使用select distinct只返回一个产品。但是，您也可以通过删除join来简化查询。不需要stores和categories表，因为您只使用id，这些id在连接表中可用。我还会使用更简单的表别名:

select distinct p."name" 
from "products" p join
"product_categories" pc
on pc."product_id" = p."id" join
"product_stores" ps
on ps."product_id" = p."id" 
where pc.category_id in ( 4,5,6,7 ) and
ps."id" = 1;

注意:你应该避免使用双引号作为标识符。如果您的表是使用它们定义的，那么我建议修复表定义。

这个查询，反过来，可能会更有效地使用exists，因为select distinct将不再需要:

select p."name" 
from "products" p
where exists (select 1
from "product_categories" pc
where pc."product_id" = p."id" andf
pc.category_id in ( 4,5,6,7 ) 
) and
exists (select 1
from "product_stores" ps
where ps."product_id" = p."id" and
ps."id" = 1
);