SQL 只返回重复的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4401326/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Return only duplicate rows
提问by user380432
I have a query that returns the following rows:
我有一个返回以下行的查询：
StateId, OrderId, OrderTime, PermitId
StateId、OrderId、OrderTime、PermitId
I need to return only the rows that are exact duplicates all across the board so each record must be exctly the same as the other record for it to be a duplicate. I would like to return both records. These reocrds are mixed in with a bunch of records that do not have duplicates...
我只需要返回完全重复的行，因此每条记录必须与其他记录完全相同才能重复。我想返回这两个记录。这些记录与一堆没有重复的记录混合在一起......
Any idea?
任何的想法？
回答by gbn
First, identify the duplicates. Second, join back to extract these rows.
首先，识别重复项。其次，重新连接以提取这些行。
A non-aggregated (or non-window/ranking) self join forms a partial cross join and gives the square of duplicates for any set of keys. Including non-duplicates too. 1 x 1 = 1 after all.
非聚合（或非窗口/排名）自联接形成部分交叉联接，并为任何一组键提供重复项的平方。也包括非重复项。毕竟 1 x 1 = 1。
SELECT
t2.*
FROM
(
SELECT
StateId, OrderId, OrderTime, PermitId
FROM
myTable
GROUP BY
StateId, OrderId, OrderTime, PermitId
HAVING
COUNT(*) >= 2
) T1
JOIN
mytable T2 ON T1.StateId = T2.StateId AND T1.OrderId = T2.OrderId AND
T1.OrderTime = T2.OrderTime AND T1.PermitId = T2.PermitId
回答by Brosto
In general, if you're just trying to see what rows have duplicate for those values...
一般来说，如果您只是想查看哪些行与这些值重复...
SELECT StateId, OrderId, OrderTime, PermitId, COUNT(*) FROM Foo
GROUP BY StateId, OrderId, OrderTime, PermitId
HAVING COUNT(*) > 1
回答by Pablo Santa Cruz
One possibility if your database server supports subqueries:
如果您的数据库服务器支持子查询，则一种可能性：
select * from your_table
where (StateId, OrderId, OrderTime, PermitId) in
( select StateId, OrderId, OrderTime, PermitId
from your_table
group by StateId, OrderId, OrderTime, PermitId
having count(1) > 1 )
