sql查询查找重复记录 [英] sql query to find the duplicate records

问题描述

什么是sql查询查找重复记录，并以降序显示，基于最高计数和id显示记录。

例如： p>

获得计数可以通过

 选择标题，计数（标题）作为cnt从kmovies组按标题顺序由cnt desc 
  

，结果将像

  title cnt 
 
 ravi 10 
 prabhu 9 
 srinu 6 
  

现在，查询结果如下所示：

  ravi 
 ravi 
 ravi 
 ... 10次
 prabhu 
 prabhu..9次
 srinu 
 srinu ... 6次
  

解决方案

如果你RDBMS支持OVER子句...

  SELECT 
 title 
 FROM 
（
选择
标题，count（*）OVER（PARTITION BY title）as cnt 
 from 
 kmovies 
 ）T 
 ORDER BY 
 cnt DESC 
  

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.

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

解决方案

If your RDBMS supports the OVER clause...

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

这篇关于sql查询查找重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！