sql - 如何在一个sql表里查找重复值?

  显示原文与译文双语对照的内容

在一个字段中查找重复项很容易:


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

这个查询会给我们 John,Sam,Tom,Tom,因为他们都有相同的email

然而,我想要的是重复相同的emailname

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

我需要的原因: 我犯了一个错误,允许插入重复的nameemail 值。 现在我需要删除/修改副本,所以我需要找到第一。

时间:


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

只对两个列进行分组

注意:ANSI标准是在 GROUP BY 中包含所有非聚集列。

MySQL允许你避免这种情况,但结果是不可预测的:

尝试这个:


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

输出:


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

(2 row(s) affected)

如果你想要dups的IDs使用这里选项:


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

输出:


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

输出:


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

(4 row(s) affected)

请尝试以下操作:


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

如果你使用 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);

尝试这里代码


WITH CTE AS

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

如果你想查看表中是否有重复行,我使用以下查询:


create table my_table(id int, name varchar(100), email varchar(100));

insert into my_table values (1, 'shekh', 'shekh@rms.com');
insert into my_table values (1, 'shekh', 'shekh@rms.com');
insert into my_table values (2, 'Aman', 'aman@rms.com');
insert into my_table values (3, 'Tom', 'tom@rms.com');
insert into my_table values (4, 'Raj', 'raj@rms.com');


Select COUNT(1) As Total_Rows from my_table 
Select Count(1) As Distinct_Rows from ( Select Distinct * from my_table) abc 

我们如何计算重复的值? 要么重复 2次,要么大于 2次。 只计算他们,而不是 group 。

简单一样简单


select COUNT(distinct col_01) from Table_01


 select emp.ename, emp.empno, dept.loc 
 from emp
 inner join dept 
 on dept.deptno=emp.deptno
 inner join
 (select ename, count(*) from
 emp
 group by ename, deptno
 having count(*)> 1)
 t on emp.ename=t.ename order by emp.ename
/

...