重复数据操作

 

SELECT * FROM TUser

 

 

一、在表中找到重复的数据

 

-- 在一列中找到重复的值
SELECT FName,COUNT(FName)
FROM TUser
GROUP BY FName
HAVING COUNT(FName)>1

 

 

-- 在多列中找到重复的值
SELECT FName,COUNT(FName),FEmail,COUNT(FEmail)
FROM TUser
GROUP BY FName,FEmail
HAVING COUNT(FName)>1 AND COUNT(FEmail)>1

 

 

二、在表中删除重复的数据

 

-- 在表中删除重复数据
DELETE t1
FROM TUser t1
INNER JOIN TUser t2 ON t1.FID < t2.FID
AND t1.FName=t2.FName AND t1.FEmail=t2.FEmail;


SELECT * FROM TUser

 

-- 在表中删除重复数据
DELETE t1
FROM TUser t1
INNER JOIN TUser t2 ON t1.FID > t2.FID
AND t1.FName=t2.FName AND t1.FEmail=t2.FEmail;

SELECT * FROM TUser