MySQL多对多关联表用IN查询产品出现重复记录如何去重

重复原因 #

多对多关联查询时，当单个产品同时匹配categories.id in (4,5,6,7)里的多个分类，和product_categories表关联时会生成多条匹配行，最终返回重复的产品记录。

解决方案 #

方案1：加DISTINCT直接去重（最简便）

直接在查询字段前加DISTINCT关键字，即可对返回的产品名称做去重，适合只需要返回产品字段的轻量查询场景：

select distinct
  "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

方案2：用EXISTS子查询（性能最优）

不需要全量关联分类表和门店表，子查询只要匹配到符合条件的关联关系就会返回结果，不会生成冗余的重复行，数据量大的时候性能远高于join后去重：

select 
  "products"."name" 
from 
  "products" 
where 
  -- 匹配门店关联
  exists (
    select 1 from "product_stores" 
    where "product_stores"."product_id" = "products"."id" 
    and "product_stores"."store_id" = 1
  )
  -- 匹配分类关联
  and exists (
    select 1 from "product_categories" 
    where "product_categories"."product_id" = "products"."id" 
    and "product_categories"."category_id" in (4,5,6,7)
  )

方案3：用GROUP BY（适合需要附加统计的场景）

如果后续需要统计每个产品符合条件的分类数、门店数等聚合指标，可以用GROUP BY去重：

select 
  "products"."name",
  count(distinct "categories"."id") as match_category_count
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
group by "products"."id", "products"."name"

方案选择建议 #

• 仅需要去重拿唯一产品列表：选方案1，写法最简单
• 数据量较大、对性能要求高：选方案2，避免生成冗余临时数据
• 需要同步做聚合统计：选方案3

内容的提问来源于stack exchange，提问作者Mòe