如何在多列中找到重复项？

Question

我想要执行类似下面的 SQL 代码：

select s.id, s.name,s.city 
from stuff s
group by s.name having count(where city and name are identical) > 1

为了产生如下结果（但忽略仅名称或仅城市匹配的情况，必须在两列中都匹配）：

id      name  city   
904834  jim   London  
904835  jim   London  
90145   Fred  Paris   
90132   Fred  Paris
90133   Fred  Paris

- NimChimpsky

Answer 1

对于名字（name）和城市（city）的配对，发现有重复的id：

select s.id, t.* 
from [stuff] s
join (
    select name, city, count(*) as qty
    from [stuff]
    group by name, city
    having count(*) > 1
) t on s.name = t.name and s.city = t.city

- Michał Powaga

---

请注意，如果“name”或“city”任一包含“null”，则它们将无法在外部查询中报告，但将在内部查询中匹配。 - Adam Parkin

如果这些值可能包含null，那么（除非我漏掉了什么）您需要将它更改为CROSS JOIN（全笛卡尔积），然后添加一个WHERE子句，例如：WHERE ((s.name = t.name) OR (s.name is null and t.name is null)) AND ((s.city = t.city) OR (s.city is null and t.city is null))。 - Adam Parkin

这个答案不会返回每个重复记录的唯一ID。相反，它将合并重复的记录为单个记录，并选择在表中首先出现的任何ID。我认为@ssarabando的答案更为恰当。 - Crayons

@Crayons 两个答案将返回相同的结果。请参见 https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=8bf900c22bbb263ef792f73e8355cdc2 和 https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=8e94833cb4dd27a1974a1a362ff2a11c - Jack B

Answer 2

 SELECT name, city, count(*) as qty 
 FROM stuff 
 GROUP BY name, city HAVING count(*)> 1

- Sunnny

---

由此，您无法知道每行的ID。 - Juan.Queiroz

将 SELECT name, city, count(*) as qty 替换为 SELECT *，以查看所有列，包括 id。 - yoyo

@yoyo，你的建议出现了错误，请提供一个完整的SQL语句，不要出现错误。 - nutty about natty

为了查看ID（至少）的最大/最小值：SELECT max(id), min(id), name, city, count(*) as qty FROM stuff GROUP BY name, city HAVING count(*)> 1 - nutty about natty

这个答案没有回答问题，因为它没有返回唯一的ID。 - Crayons

Answer 3

像这样做就可以了，不知道性能如何，所以要进行一些测试。

select
  id, name, city
from
  [stuff] s
where
1 < (select count(*) from [stuff] i where i.city = s.city and i.name = s.name)

- ssarabando

---

这是一个被低估的答案，我认为它是最好的答案。这个答案识别了重复项，同时返回单独的记录和它们的唯一ID。标记的答案将结果分组，这意味着您实际上无法通过它们的唯一ID识别重复项，因此数据集不太有用。 - Crayons

@Crayons 两个答案将返回相同的结果。请参见 https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=8bf900c22bbb263ef792f73e8355cdc2 和 https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=8e94833cb4dd27a1974a1a362ff2a11c - Jack B

我喜欢这个答案，它非常简单。 - undefined

Answer 4

使用count(*) over(partition by...)可以简单高效地定位不必要的重复项，同时列出所有受影响的行和所需的所有列：

SELECT
    t.*
FROM (
    SELECT
        s.*
      , COUNT(*) OVER (PARTITION BY s.name, s.city) AS qty
    FROM stuff s
    ) t
WHERE t.qty > 1
ORDER BY t.name, t.city

尽管大多数最近的关系型数据库管理系统版本支持count(*) over(partition by...)，但MySQL V 8.0引入了“窗口函数”，如下所示（在MySQL 8.0中）

CREATE TABLE stuff(
   id   INTEGER  NOT NULL
  ,name VARCHAR(60) NOT NULL
  ,city VARCHAR(60) NOT NULL
);

INSERT INTO stuff(id,name,city) VALUES 
  (904834,'jim','London')
, (904835,'jim','London')
, (90145,'Fred','Paris')
, (90132,'Fred','Paris')
, (90133,'Fred','Paris')

, (923457,'Barney','New York') # not expected in result
;

SELECT
    t.*
FROM (
    SELECT
        s.*
      , COUNT(*) OVER (PARTITION BY s.name, s.city) AS qty
    FROM stuff s
    ) t
WHERE t.qty > 1
ORDER BY t.name, t.city

    id | name | city   | qty
-----: | :--- | :----- | --:
 90145 | Fred | Paris  |   3
 90132 | Fred | Paris  |   3
 90133 | Fred | Paris  |   3
904834 | jim  | London |   2
904835 | jim  | London |   2

db<>fiddle 这里

窗口函数。 MySQL现在支持窗口函数，它们针对查询的每一行执行计算，使用与该行相关的行。这些函数包括RANK()、LAG()和NTILE()等函数。此外，现在可以将几个现有的聚合函数用作窗口函数；例如，SUM()和AVG()。有关更多信息，请参见第12.21节“窗口函数”。

- Paul Maxwell

Answer 5

我发现这种方法相当灵活/高效。

select 
    s1.id
    ,s1.name
    ,s1.city 
from 
    stuff s1
    ,stuff s2
Where
    s1.id <> s2.id
    and s1.name = s2.name
    and s1.city = s2.city

- MattD

---

选择 distinct ... 可能是这里需要/缺少的，对吗？ - nutty about natty

Answer 6

SELECT Feild1, Feild2, COUNT(*)
FROM table name
GROUP BY Feild1, Feild2
HAVING COUNT(*)>1

这将为您提供所有答案。

- Arunav dutta gupta

Answer 7

你需要自我连接数据并匹配姓名和城市，然后按计数分组。

select 
   s.id, s.name, s.city 
from stuff s join stuff p ON (
   s.name = p.city OR s.city = p.name
)
group by s.name having count(s.name) > 1

- Anja

---

在SQL Server中失败：所有非聚合列都必须在GROUP BY中。 - gbn

Answer 8

根据问题，提问者希望对列进行分组，并获取不属于分组列的其他列。

所以常规的group by + having可能不起作用。

我建议使用EXISTS子查询和HAVING。

我们可以尝试在子查询中添加想要标记为重复的列。

SELECT s.id, s.name,s.city 
FROM stuff s
WHERE EXISTS (
   SELECT 1
   FROM stuff ss
   WHERE 
      s.name = ss.name
   AND
      s.city = ss.city
   GROUP BY ss.name,ss.city
   HAVING COUNT(*) > 1
)

如果我们创建一个合适的索引，可能会比使用join更好地提高性能。

CREATE INDEX IX_name ON stuff (
    name,
    city
);

我们可以使用带过滤条件的 COUNT 窗口函数，并在 PARTITION BY 部分添加分组列来实现此方式。

SELECT s.id, s.name,s.city 
FROM (
   SELECT *,COUNT(*) OVER(PARTITION BY name,city) cnt
   FROM stuff 
) s
WHERE cnt > 1

sqlfiddle

- D-Shih

Answer 9

很高兴在这里介绍另一种使用 Cross Apply 实现所需输出的方法，如下所示：

select s.* from stuff s
cross apply(
    select name, city from stuff
    group by name, city
    having Count(*) > 1) x
where s.name = x.name and s.city=x.city

- Md. Suman Kabir

Answer 10

给定一个包含70列的暂存表，其中只有4列是重复的，这段代码将返回冲突的列：

SELECT 
    COUNT(*)
    ,LTRIM(RTRIM(S.TransactionDate)) 
    ,LTRIM(RTRIM(S.TransactionTime))
    ,LTRIM(RTRIM(S.TransactionTicketNumber)) 
    ,LTRIM(RTRIM(GrossCost)) 
FROM Staging.dbo.Stage S
GROUP BY 
    LTRIM(RTRIM(S.TransactionDate)) 
    ,LTRIM(RTRIM(S.TransactionTime))
    ,LTRIM(RTRIM(S.TransactionTicketNumber)) 
    ,LTRIM(RTRIM(GrossCost)) 
HAVING COUNT(*) > 1

.

- Don G.