如何选择重复项才能得到正确的结果？

Question

你能帮我写一个工作sql查询吗？

我需要选择所有重复的link_rewrite。只有当id_shop和id_lang相同时，它才是重复的。

查看完整代码：https://github.com/Ha99y/prestashopCleanURLs/blob/master/PS15/cleanurls.php#L49

-------------------------------------------------------
| id_product  |  id_shop  |  id_lang  |  link_rewrite |
-------------------------------------------------------
|     1       |    1      |     1     |   ipod-nano   |
|     1       |    1      |     2     |   ipod-nano   |
|     2       |    1      |     1     |   ipod-nano   |
|     2       |    1      |     2     |   ipod-nano   |
|     8       |    2      |     1     |   ipod-nano   |
|     8       |    2      |     2     |   ipod-nano   |
-------------------------------------------------------

SQL：

SELECT * FROM `ps_product_lang`
WHERE `link_rewrite`
IN (SELECT `link_rewrite` FROM `ps_product_lang`
GROUP BY `link_rewrite`, `id_lang`
HAVING count(`link_rewrite`) > 1)

。

感谢您的任何帮助

Answer 1

在我看来，您的查询中唯一缺少的就是GROUP BY子句中的id_shop。

SELECT * FROM `ps_product_lang`
WHERE `link_rewrite`
IN (SELECT `link_rewrite` FROM `ps_product_lang`
GROUP BY `link_rewrite`, `id_lang`, `id_shop`
HAVING COUNT(`link_rewrite`) > 1)

Answer 2

您声明的要求是“只有当id_shop和id_lang相同时才是重复的”。这意味着，在某个地方，您的查询需要包含如下内容：

select id_shop, id_lang, count(*) records
from the applicable tables
group by id_shop, id_lang
having count(*) > 1

你只需要找到正确的位置来放置它。

Answer 3

在MySQL中执行此操作的最有效方法是使用exists

SELECT *
FROM `ps_product_lang` pl
WHERE exists (select 1 
              from `ps_product_lang` pl2
              where pl2.id_shop = pl.id_shop and
                    pl2.id_lang = pl.id_lang and
                    pl2.id_product <> pl.id_product
             );