SQL查询以查找重复记录

Question

如何编写 SQL 查询以查找重复记录并按照记录数量和 ID 的降序显示记录。

例如：

可以使用以下语句获取记录数量

select title, count(title) as cnt from kmovies group by title order by cnt desc

结果将会像这样

title cnt

ravi   10
prabhu  9
srinu   6

现在查询语句应该是什么，才能得到以下类似的结果：

ravi
ravi
ravi
...10 times
prabhu
prabhu..9 times
srinu
srinu...6 times

- Tan

Answer 1

如果您的关系型数据库管理系统支持OVER子句...

SELECT
   title
FROM
    (
    select
       title, count(*) OVER (PARTITION BY title) as cnt
    from
      kmovies
    ) T
ORDER BY
   cnt DESC

- gbn

Answer 2

你可以在一次查询中完成：

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

- Charles Bretana

Answer 3

这个查询使用Group By 和 Having 子句，允许你选择（定位和列出）每个重复记录。 As 子句是一个便利，用于在 select 和 Order By 子句中引用 Quantity，但并不是真正获取重复行的部分。

Select
    Title,
    Count( Title ) As [Quantity]
   From
    Training
   Group By
    Title
   Having 
    Count( Title ) > 1
   Order By
    Quantity desc

- Russ

Answer 4

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

- Manoj

Answer 5

你不能用一个简单的单一查询来完成它，但是这样做可以：

select title
from kmovies
where title in (
    select title
    from kmovies
    group by title
    order by cnt desc
    having count(title) > 1
)

- Marc B

---

这个不会给出正确的结果，因为它没有按照COUNT(*)降序重复行。这里没有最外层的ORDER BY，所以排序是随意的。而且还有2个其他的答案提供了一个单查询解决方案。 - gbn