MySQl - 查找重复的交叉关系表

Question

我有三个表:

`products`
----------
`product_id` (int)
`sku` (varchar=50)

`websites`
----------
`website_id` (int)

`website_products`
----------
`website_id` (int)
`product_id` (int)

我正在尝试根据每个网站的 SKU 查找重复产品。到目前为止，这是我的尝试，但很确定它不起作用:

SELECT b.`website_id`, a.`product_id` ,COUNT(a.*) AS count
FROM `products` AS a 
     LEFT JOIN `website_products` AS b 
         ON ( a.`product_id` = b.`product_id` ) 
WHERE 1
GROUP BY a.`sku` 
HAVING count > 1
ORDER BY b.`website_id`

有人有什么想法吗？

Answer 1

不知道这是不是你要找的。也许通过一些示例数据我可以改进我的查询。这样，您就可以获取网站中某个 SKU 的所有产品(对于具有多个产品的 SKU)

SELECT 
    b.`website_id`, a.sku, 
    group_concat(DISTINCT a.`product_id`) as sku_products 
FROM `products` AS a 
     LEFT JOIN `website_products` AS b 
         ON ( a.`product_id` = b.`product_id` ) 
GROUP BY b.`website_id`, a.`sku` 
HAVING count(distinct a.`product_id` ) > 1
ORDER BY b.`website_id`