sql 操作重复数据集合

a.如果有ID字段，就是具有唯一性的字段

 

delect table where id not in (

select max(id) from table group by col1,col2,col3...
)
group by 子句后跟的字段就是你用到判斷重复的字段

 

b.,如果是判斷所有字段
select * into #aa from table group by id1,id2,....
delete table table
insert into table
select * from #aa

 

c.如果表中有ID的情況

select identity(int,1,1) as id,* into #temp from tabel
delect # where id not in (
select max(id) from # group by col1,col2,col3...)
delect table
inset into table(...)
select ..... from #temp

col1+','+col2+','...col5 組合主鍵

select * from table where col1+','+col2+','...col5 in (

select max(col1+','+col2+','...col5) from table
where having count(*)>1
group by col1,col2,col3,col4
)
group by 子句后跟的字段就是你用到判斷重复的字段

d.
select identity(int,1,1) as id,* into #temp from tabel
select * from #temp where id in (
select max(id) from #emp where having count(*)>1 group by col1,col2,col3...)

e.
alter table yourtable add rownum int identity(1,1)
go
delete from yourtable where rownum not in (select min(rownum ) from yourtable group by 你重复的字段名)
go
alter table yourtable drop column rownum
go

f.
alter table 表 add newfield int identity(1,1)
delete 表
where newfield not in(
select min(newfield) from 表 group by 除newfield外的所有字段
)

alter table 表 drop column newfield

g.
-- 刪除表中重復的記錄
DELETE delete1
FROM tabTest delete1
JOIN tabTest delete2
ON delete1.student_id=delete2.student_id AND delete1.course_id=delete2.course_id AND delete1.id>delete2.id