Mysql与2个其他表的查询关系的重复结果,IN运算符

9jyewag0  于 2022-11-21  发布在  Mysql
关注(0)|答案(1)|浏览(158)

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

  • 通过表 product_store 将产品与 stores 进行多对多关联
  • 通过表 product_category 将产品与 * 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
13z8s7eq

13z8s7eq1#

您可以使用select distinct只返回一个产品。但是,您也可以通过删除join来简化查询。不需要storescategories表,因为您只使用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
             );

相关问题