如何找到跨多列的重复项？

所以我想做一些像下面这样的 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
223943 次浏览
小开

你必须自己加入东西，并匹配名字和城市。然后分组计数。

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
小开

像这样的东西会有用的。不知道性能如何，所以做一些测试吧。

select
id, name, city
from
[stuff] s
where
1 < (select count(*) from [stuff] i where i.city = s.city and i.name = s.name)
小开
最佳答案

对 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
小开 
 SELECT name, city, count(*) as qty
FROM stuff
GROUP BY name, city HAVING count(*)> 1
小开

给定一个有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

.

小开

使用 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

虽然最新的 RDBMS 版本支持 count(*) over(partition by...) MySQL V8.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

分贝 < > 小提琴 给你

窗口功能。MySQL 现在支持窗口函数，对于查询中的每一行，使用相关的行执行计算 这些函数包括 RANK ()、 LAG ()和 NTILE ()。 此外，几个现有的聚合函数现在可以用作 窗口函数; 例如，SUM ()和 AVG (), 见 第12.21节“窗函数”。

小开

这篇文章有点晚了，但我发现这种方法非常灵活/高效

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
小开 
SELECT Feild1, Feild2, COUNT(*)
FROM table name
GROUP BY Feild1, Feild2
HAVING COUNT(*)>1

这会给你所有的答案。

小开

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

因此，常规的 group by + having可能无法工作。

我将对 HAVING使用 EXISTS子查询。

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

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

小开

很高兴在这里添加另一种使用 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

提交答案