Question

sql 删除重复行怎么写

rt
就是删除数据库中完全一样的，仅仅保留一条。

Answer 1

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)

Answer 2

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

Answer 3

假设表中只有两个字段 一个是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 )

Answer 4

🍀🌾🍃🍂🍁🎍

Answer 5

什么删除重复行？
您说清楚