如何在表中找到所有重复记录？ [英] How do I find all the duplicate records in a table?

问题描述

我想在多个列的表格中找到重复的值。



但是输出看起来像



姓名城市名称城市

chandu美国chandu美国

chandu美国chandu美国

汤姆英国汤姆英国

汤姆英国汤姆英国



我怎样才能只显示两列？有没有简单的方法来查找所有重复的记录？



我尝试过：

I want to find the duplicate values in my table on multiple columns.

But the output looks like

Name city Name city
chandu america chandu america
chandu america chandu america
tom UK tom UK
tom UK tom UK

How can i show only two columns? Is there any easy way of finding all duplicate records?

What I have tried:

select * from EMP a join(select Name,city from EMP group by Name,city having count (*)>1)b
on a.Name=b.Name
and a.city=b.city

推荐答案

您只需要这样：

You need only this:

SELECT Name, city
FROM EMP
GROUP BY Name, city
HAVING COUNT(*)>1

另一个 SELECT 语句是多余的！

要仅显示两个列，请减少所选列：

To show only two colums, reduce the columns you select:

select a.Name, a.city from EMP a join(select Name,city from EMP group by Name,city having count (*)>1)b
on a.Name=b.Name
and a.city=b.city

To仅显示不同的结果：

To show distinct results only:

select distinct a.Name, a.city from EMP a join(select Name,city from EMP group by Name,city having count (*)>1)b
on a.Name=b.Name
and a.city=b.city

查找重复记录

To find duplicate records

select Name,city,count(*) Cnt from TableName group by Name,city having count(*)>1

从表中删除重复记录

To delete duplicate records from table

;with del as
(select row_number() over(partition by Name,city order by Name,city) as rownumber,* from TableName)
delete from del where rownumber >1

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