sql 删除重复行怎么写

1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

select * from people where peopleId in (select peopleId from people group by peopleId having count (peopleId) > 1)

 2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录

delete from people where peopleId in (select peopleId from people group by peopleId having count (peopleId) > 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

3、查找表中多余的重复记录（多个字段）

select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

 4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录

delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录

select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

 

 

 

 

 

 

假设表中只有两个字段 一个是Coursename 一个是ID
A.
DELETE FROM @TEMP_TABLE
WHERE COURSENAME  IN (SELECT   COURSENAME  FROM @TEMP_TABLE  GROUP  BY   COURSENAME   HAVING  COUNT(COURSENAME) > 1)
AND ID NOT IN (SELECT MIN(ID) FROM   @TEMP_TABLE  GROUP BY COURSENAME  HAVING COUNT(COURSENAME )>1)
假设表中只有两个字段一个是NAME1,一个是CODE
B.
SELECT *,IID =CONVERT(INT, ROW_NUMBER() OVER(PARTITION BY NAME1 ORDER BY  CODE) )  INTO #B FROM #A  
SELECT * FROM #B
DELETE  FROM #A WHERE CODE NOT IN (SELECT CODE FROM #B WHERE IID=1 )

 

方案是 先distinct 出来，然后插入到临时表里面
然后把所有重复行删除
然后再把临时表 插回去！