使用SQL Server查找表中的重复记录

Question

我正在验证一个电子商务站点的事务级数据表，并查找确切的错误。

我想要您的帮助，在 SQL Server 中的一个包含 50 列的表中查找重复记录。

假设我的数据如下：

OrderNo shoppername amountpayed city Item       
1       Sam         10          A    Iphone
1       Sam         10          A    Iphone--->>Duplication to be detected
1       Sam         5           A    Ipod
2       John        20          B    Macbook
3       John        25          B    Macbookair
4       Jack        5           A    Ipod

假设我使用以下查询：

Select shoppername,count(*) as cnt
from dbo.sales
having count(*) > 1
group by shoppername

会返回给我

Sam  2
John 2

但我不想仅查找1或2列中的重复项。我希望在我的所有数据列中一起查找重复项。我希望结果如下：

1       Sam         10          A    Iphone

- Sahil

Answer 1

with x as   (select  *,rn = row_number()
            over(PARTITION BY OrderNo,item  order by OrderNo)
            from    #temp1)

select * from x
where rn > 1

您可以通过将选择语句替换为以下内容来删除重复项：

SELECT DISTINCT

delete x where rn > 1

- Sathya Narayanan

---

太好了。我知道在Oracle中可以这样做，但不知道在SQL Server中也可以实现。 - GolezTrol

对于任何好奇的人，这是我在 Oracle 中编写它的方式：with x as (select t.*, row_number() over(PARTITION BY OrderNo, item order by OrderNo) as rn from t_dcf t) select * from x where rn > 1; - Juan

值得被选为答案！ - Daniël Tulp

Answer 2

SELECT OrderNo, shoppername, amountPayed, city, item, count(*) as cnt
FROM dbo.sales
GROUP BY OrderNo, shoppername, amountPayed, city, item
HAVING COUNT(*) > 1

- Eugene

Answer 3

SQL> SELECT JOB,COUNT(JOB) FROM EMP GROUP BY JOB;

JOB       COUNT(JOB)
--------- ----------
ANALYST            2
CLERK              4
MANAGER            3
PRESIDENT          1
SALESMAN           4

- MUEKSH KUMAR

Answer 4

只需将所有字段添加到查询中，并记得在 Group By 中添加它们即可。

Select shoppername, a, b, amountpayed, item, count(*) as cnt
from dbo.sales
group by shoppername, a, b, amountpayed, item
having count(*) > 1

- GolezTrol

---

我有50个字段。那么有没有办法在不逐个编写所有字段名称的情况下找出重复记录！！ - Sahil

在表格上右键，选择：脚本表格为 >> 选择 >> 新查询编辑器窗口。现在您已经有了SELECT列表，请将其复制并粘贴到GROUP BY部分。 - JerryOL

Answer 5

获取多条记录的列表，请使用以下命令。

select field1,field2,field3, count(*)
  from table_name
  group by field1,field2,field3
  having count(*) > 1

- Abhinav Singh

Answer 6

试试这个替代方案

SELECT MAX(shoppername), COUNT(*) AS cnt
FROM dbo.sales
GROUP BY CHECKSUM(*)
HAVING COUNT(*) > 1

首先阅读 CHECKSUM 函数的相关信息，因为可能会存在重复。

- wqw

Answer 7

试试这个

with T1 AS
(
SELECT LASTNAME, COUNT(1) AS 'COUNT' FROM Employees GROUP BY LastName HAVING  COUNT(1) > 1
)
SELECT E.*,T1.[COUNT] FROM Employees E INNER JOIN T1 ON T1.LastName = E.LastName

- Ragavendhran N

Answer 8

您可以使用以下方法查找输出

 with Ctec AS
 (
select *,Row_number() over(partition by name order by Name)Rnk
 from Table_A
)
select  Name from ctec
where rnk>1

select name from Table_A
 group by name
 having count(*)>1

- sampath acharya

Answer 9

with x as (
select shoppername,count(shoppername)
              from sales
              having count(shoppername)>1
            group by shoppername)
select t.* from x,win_gp_pin1510 t
where x.shoppername=t.shoppername
order by t.shoppername

- user5758159

Answer 10

首先，我怀疑结果不准确？原始表格中好像有三个'Sam'。但这对问题本身并不重要。

然后，我们来谈谈问题本身。基于您的表格，显示重复值的最佳方法是使用count(*)和Group by子句。查询将如下所示： SELECT OrderNo, shoppername, amountPayed, city, item, count(*) as RepeatTimes FROM dbo.sales GROUP BY OrderNo, shoppername, amountPayed, city, item HAVING COUNT(*) > 1 原因是从您的表格中所有列一起唯一标识每条记录，这意味着仅当每列的所有值完全相同时，记录才会被视为重复，并且您要显示重复记录的所有字段，因此group by将不会错过任何列，否则是因为您只能在“group by”子句中选择参与的列。

现在我想给您一个关于With...Row_Number()Over(...)的例子，它使用表达式和Row_Number函数。

假设你有一个几乎相同的表格，但多了一列名为Shipping Date的列，而其值可能不同于其他行。如下所示：

OrderNo shoppername amountpayed city Item Shipping Date
1 Sam 10 A Iphone 2016-01-01 1 Sam 10 A Iphone 2016-02-02 1 Sam 5 A Ipod 2016-03-03 2 John 20 B Macbook 2016-04-04 3 John 25 B Macbookair 2016-05-05 4 Jack 5 A Ipod 2016-06-06

请注意，如果您仍将所有列视为一个单位，则第2行不是重复行。但如果您在此情况下也要将它们视为重复行怎么办？您应该使用With...Row_Number()Over(...)，查询将如下所示：

WITH TABLEEXPRESSION AS (SELECT *,ROW_NUMBER() OVER (PARTITION BY OrderNo, shoppername, amountPayed, city, item ORDER BY [Shipping Date] as Identifier) --如果您认为运送日期较晚的是重复项 FROM dbo.sales) SELECT * FROM TABLEEXPRESSION WHERE Identifier !=1 --或使用 '>1'

以上查询将返回包括Shipping Date在内的结果，例如：

OrderNo shoppername amountpayed city Item Shipping Date Identifier 1 Sam 10 A Iphone 2016-02-02 2

请注意，此结果与2016-01-01不同，2016-02-02被过滤的原因是 PARTITION BY OrderNo, shoppername, amountPayed, city, item ORDER BY [Shipping Date] as Identifier，而Shipping Date并不是需要处理重复记录的列之一，这意味着2016-02-02仍然可能是您问题的完美答案。

现在简要总结一下，使用count(*)和Group by子句一起是最好的选择，当您只想显示所有来自Group by子句的列作为结果时，否则您将错过那些不参与group by的列。

而对于With...Row_Number()Over(...)，它适用于您想要查找重复记录的每种情况，但是与前者相比，编写查询有点复杂且有点过度设计。

如果您的目的是从表中删除重复记录，则必须使用后面的WITH...ROW_NUMBER()OVER(...)...DELETE FROM...WHERE。

希望这可以帮助您！

- LONG