返回所有重复的行

Question

我写了这段代码来查找重复项，它能正常工作：

SELECT *
FROM StyleTable 
GROUP BY Color
HAVING count(*) > 1 

问题是，它只返回了重复行中的一个。有可能返回所有重复行吗？我猜可能与“GROUP BY”有关，但我不知道如何修改它。我不想删除这些值，只想将它们返回。

- XSL

---

我认为这个 SQL 不会运行，除非 Color 是 STyleTable 表中唯一的列。 - theglauber

Answer 1

我认为你需要再次加入表格才能获取重复项。类似于：

SELECT * 
FROM StyleTable 
WHERE Color IN (
  SELECT Color  
  FROM StyleTable   
  GROUP BY Color  
  HAVING count(*) > 1 
)     

- Peter Wishart

Answer 2

SELECT s.*
    FROM StyleTable s
        INNER JOIN (SELECT Color
                        FROM StyleTable
                        GROUP BY Color
                        HAVING COUNT(*) > 1) q
            ON s.Color = q.Color

- Joe Stefanelli

Answer 3

WITH cte AS ( SELECT FirstName, LastName,COUNT(*) occurrences FROM Customer GROUP BY FirstName, LastName HAVING COUNT(*) > 1)
SELECT * FROM Customer INNER JOIN cte ON 
        cte.FirstName = Customer.FirstName AND 
        cte.LastName = Customer.LastName
ORDER BY Customer.FirstName, Customer.LastName

- Suneel Kumar

---

如果您不介意的话，通常最好简要解释一下您的答案及其与其他答案的用途。 - Kevin