SQL sql查询查找重复记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6779607/
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 query to find the duplicate records
提问by Tan
what is the sql query to find the duplicate records and display in descending, based on the highest count and the id display the records.
根据最高计数和 id 显示记录，查找重复记录并降序显示的 sql 查询是什么。
for example:
例如：
getting the count can be done with
得到计数可以完成
select title, count(title) as cnt from kmovies group by title order by cnt desc
and the result will be like
结果会像
title cnt
ravi 10
prabhu 9
srinu 6
now what is the query to get the result like below:
现在得到如下结果的查询是什么：
ravi
ravi
ravi
...10 times
prabhu
prabhu..9 times
srinu
srinu...6 times
回答by gbn
If your RDBMS supports the OVER clause...
如果您的 RDBMS 支持 OVER 子句...
SELECT
title
FROM
(
select
title, count(*) OVER (PARTITION BY title) as cnt
from
kmovies
) T
ORDER BY
cnt DESC
回答by Charles Bretana
You can do it in a single query:
您可以在单个查询中完成：
Select t.Id, t.title, z.dupCount
From yourtable T
Join
(select title, Count (*) dupCount
from yourtable
group By title
Having Count(*) > 1) z
On z.title = t.Title
order By dupCount Desc
回答by Russ
This query uses the Group Byand and Havingclauses to allow you to select (locate and list out) for each duplicate record. The Asclause is a convenience to refer to Quantityin the selectand Order Byclauses, but is not really part of getting you the duplicate rows.
此查询使用Group Byand 和Having子句允许您为每个重复记录选择（定位并列出）。该As子句Quantity在selectandOrder By子句中引用起来很方便，但实际上并不是获取重复行的一部分。
Select
Title,
Count( Title ) As [Quantity]
From
Training
Group By
Title
Having
Count( Title ) > 1
Order By
Quantity desc
回答by Manoj
select distinct title, (
select count(title)
from kmovies as sub
where sub.title=kmovies.title) as cnt
from kmovies
group by title
order by cnt desc
回答by Marc B
You can't do it as a simple single query, but this would do:
您不能将其作为简单的单个查询来执行，但可以这样做：
select title
from kmovies
where title in (
select title
from kmovies
group by title
order by cnt desc
having count(title) > 1
)
