MySQL 查詢從 2 列重複

我需要根據 (display_name) 列從 2 列 (agents_name) 和 (estates_name) 查詢表中的重複項。

我嘗試了以下方法：

SELECT id, display_name, author_table.agents_name, author_table.estates_name, agents_name, estates_name
FROM author_table

INNER JOIN(
SELECT agents_name and estates_name
FROM author_table
GROUP BY agents_name and estates_name
HAVING COUNT(agents_name) >1 and COUNT(estates_name)>1
)temp ON author_table.agents_name and author_table.estates_name = temp.estates_name; 

最後一個“句子”應該創建一個臨時表，但我收到錯誤程式碼：1054，未知列 temp.estates_name

為您的表使用別名，尤其是當您自加入時

SELECT a1.id, a1.display_name, a1.agents_name, a1.estates_name, t.agents_name, t.estates_name
FROM author_table a1
INNER JOIN(
SELECT agents_name.lestates_name
FROM author_table
GROUP BY agents_name and estates_name
HAVING COUNT(agents_name) >1 AND COUNT(estates_name)>1
)temp t ON a1.agents_name = t.agents_name AND a1.estates_name = t.estates_name; 

也許你需要（重複對）

SELECT author_table.*
FROM author_table
NATURAL JOIN ( SELECT agents_name, estates_name
              FROM author_table
              GROUP BY agents_name, estates_name
              HAVING COUNT(*) > 1 ) temp; 

?

---

或者，也許，你需要這個（分開重複）

SELECT author_table.*
FROM author_table
NATURAL JOIN ( SELECT agents_name
              FROM author_table
              GROUP BY agents_name
              HAVING COUNT(*) > 1 ) temp1
NATURAL JOIN ( SELECT estates_name
              FROM author_table
              GROUP BY estates_name
              HAVING COUNT(*) > 1 ) temp2; 

?

引用自：https://dba.stackexchange.com/questions/258811