sql尋找重複資料

1.尋找重複資料表的id以及重複資料的條數

select max(id) as nid,count(id) as 重複條數 from tableName

group by linkname Having Count(*) > 1

 

2.尋找重複資料表的主鍵

 

 

select id from UsedCar_Articles

 

where id not in (

 

select min(id) as nid from UsedCar_Articles

 

group by ArticleTitle  Having Count(id) > 1

 

)

 

3.重複資料刪除的資料

 

delete from UsedCar_Articles

 

where id not in (

 

select min(id) as nid from UsedCar_Articles

 

group by ArticleTitle  Having Count(id) > 1

 

)

4.插入重複的資料

INSERT Articles (ClassID, ArticleTitle,ArticleInfo,

ArticleClicks,IsHot,IsTop,IssueTime,

SiteProvinceID,SiteCityID)

SELECT ClassID, ArticleTitle,ArticleInfo,

ArticleClicks,IsHot,IsTop,IssueTime,

SiteProvinceID,SiteCityID

FROM Articles