题 在SQL表中查找重复值


它很容易找到 duplicates 有一个字段:

SELECT name, COUNT(email) 
FROM users
GROUP BY email
HAVING COUNT(email) > 1

所以,如果我们有一张桌子

ID   NAME   EMAIL
1    John   asd@asd.com
2    Sam    asd@asd.com
3    Tom    asd@asd.com
4    Bob    bob@asd.com
5    Tom    asd@asd.com

这个查询会给我们 约翰,山姆,汤姆,汤姆 因为他们都有相同的 email

但是,我想要的是获得相同的重复 email 和 name

也就是说,我想得到“汤姆”,“汤姆”。

我需要这个的原因:我犯了一个错误,并允许插入重复 name 和 email 值。现在我需要 删除/修改 重复,所以我需要  他们先。


1307
2018-04-07 18:17


起源


我不认为它会让你在第一个样本中选择名称,因为它不在聚合函数中。 “匹配电子邮件地址及其名称的数量是多少”是一些棘手的逻辑...... - sXe
嗯......这个问题是如何实际进入8月18日的stackoverflow“新闻”字母的? - syck


答案:


SELECT
    name, email, COUNT(*)
FROM
    users
GROUP BY
    name, email
HAVING 
    COUNT(*) > 1

只需在两个列上分组。

注意:较旧的ANSI标准是在GROUP BY中包含所有非聚合列,但这已经改变了 “功能依赖”

在关系数据库理论中,函数依赖性是来自数据库的关系中的两组属性之间的约束。换句话说,功能依赖是描述关系中属性之间关系的约束。

支持不一致:


2131
2018-04-07 18:20



我总是空白的部分是HAVING。哪里不行! - webXL
@webXL WHERE适用于单个记录HAVING适用于组 - bjan
@gbn是否可以在结果中包含Id?然后,之后删除这些重复项会更容易。 - user797717
@ user797717:您需要拥有MIN(ID),然后删除ID值,而不是最后的MIN(ID)值 - gbn
@gbn完美。谢谢。我现在可以看到ID了。 - user797717


尝试这个:

declare @YourTable table (id int, name varchar(10), email varchar(50))

INSERT @YourTable VALUES (1,'John','John-email')
INSERT @YourTable VALUES (2,'John','John-email')
INSERT @YourTable VALUES (3,'fred','John-email')
INSERT @YourTable VALUES (4,'fred','fred-email')
INSERT @YourTable VALUES (5,'sam','sam-email')
INSERT @YourTable VALUES (6,'sam','sam-email')

SELECT
    name,email, COUNT(*) AS CountOf
    FROM @YourTable
    GROUP BY name,email
    HAVING COUNT(*)>1

OUTPUT:

name       email       CountOf
---------- ----------- -----------
John       John-email  2
sam        sam-email   2

(2 row(s) affected)

如果你想要复制的ID使用这个:

SELECT
    y.id,y.name,y.email
    FROM @YourTable y
        INNER JOIN (SELECT
                        name,email, COUNT(*) AS CountOf
                        FROM @YourTable
                        GROUP BY name,email
                        HAVING COUNT(*)>1
                    ) dt ON y.name=dt.name AND y.email=dt.email

OUTPUT:

id          name       email
----------- ---------- ------------
1           John       John-email
2           John       John-email
5           sam        sam-email
6           sam        sam-email

(4 row(s) affected)

要删除重复项,请尝试:

DELETE d
    FROM @YourTable d
        INNER JOIN (SELECT
                        y.id,y.name,y.email,ROW_NUMBER() OVER(PARTITION BY y.name,y.email ORDER BY y.name,y.email,y.id) AS RowRank
                        FROM @YourTable y
                            INNER JOIN (SELECT
                                            name,email, COUNT(*) AS CountOf
                                            FROM @YourTable
                                            GROUP BY name,email
                                            HAVING COUNT(*)>1
                                        ) dt ON y.name=dt.name AND y.email=dt.email
                   ) dt2 ON d.id=dt2.id
        WHERE dt2.RowRank!=1
SELECT * FROM @YourTable

OUTPUT:

id          name       email
----------- ---------- --------------
1           John       John-email
3           fred       John-email
4           fred       fred-email
5           sam        sam-email

(4 row(s) affected)

275
2018-04-07 18:22





尝试这个:

SELECT name, email
FROM users
GROUP BY name, email
HAVING ( COUNT(*) > 1 )

89
2018-04-07 18:20





如果你想删除重复项,这里有一个更简单的方法,而不是在三重子选择中找到偶数/奇数行:

SELECT id, name, email 
FROM users u, users u2
WHERE u.name = u2.name AND u.email = u2.email AND u.id > u2.id

所以要删除:

DELETE FROM users
WHERE id IN (
    SELECT id/*, name, email*/
    FROM users u, users u2
    WHERE u.name = u2.name AND u.email = u2.email AND u.id > u2.id
)

更容易阅读和理解恕我直言

注意: 唯一的问题是您必须执行请求,直到没有删除任何行,因为每次只删除每个副本中的一个


42
2018-03-14 14:22



不错,易读;我想找到一种方法,一次删除多个重复的行。 - Dickon Reed
这对我来说不起作用 You can't specify target table 'users' for update in FROM clause - Whitecat
@Whitecat看起来像一个简单的MySQL问题: stackoverflow.com/questions/4429319/... - AncAinu
对我不利。我得到:“DBD :: CSV :: st执行失败:在/Users/hornenj/perl5/perlbrew/perls/perl-5.26.0/lib/site_perl/5.26中的哈希元素中使用未初始化的值$ _ [1]。 0 / SQL / Eval.pm第43行“ - Nigel Horne


请尝试以下方法:

SELECT * FROM
(
    SELECT Id, Name, Age, Comments, Row_Number() OVER(PARTITION BY Name, Age ORDER By Name)
        AS Rank 
        FROM Customers
) AS B WHERE Rank>1

30
2017-12-31 10:07



对SELECT *略有改动帮我解决了一小时的搜索问题。我之前从未使用过OVER(PARTITION BY。在SQL中,有多少种方法可以做同样的事情,我从未停止过惊讶! - Joe Ruder


 SELECT name, email 
    FROM users
    WHERE email in
    (SELECT email FROM users
    GROUP BY email 
    HAVING COUNT(*)>1)

22
2017-07-22 07:12





派对有点晚了,但我找到了一个很酷的解决方法来找到所有重复的ID:

SELECT GROUP_CONCAT( id )
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

17
2017-11-17 10:21



似乎是一个语法糖工作。很好找。 - Chef_Code
请记住 GROUP_CONCAT 将在预定长度后停止,因此您可能无法获得全部 id秒。 - v010dya
GROUP_CONCAT在PostgreSQL中不起作用 - Clint Eastwood


试试这段代码

WITH CTE AS

( SELECT Id, Name, Age, Comments, RN = ROW_NUMBER()OVER(PARTITION BY Name,Age ORDER BY ccn)
FROM ccnmaster )
select * from CTE 

15
2017-09-13 04:03





如果您使用Oracle,这种方式更可取:

create table my_users(id number, name varchar2(100), email varchar2(100));

insert into my_users values (1, 'John', 'asd@asd.com');
insert into my_users values (2, 'Sam', 'asd@asd.com');
insert into my_users values (3, 'Tom', 'asd@asd.com');
insert into my_users values (4, 'Bob', 'bob@asd.com');
insert into my_users values (5, 'Tom', 'asd@asd.com');

commit;

select *
  from my_users
 where rowid not in (select min(rowid) from my_users group by name, email);

13
2018-06-16 08:50