＋设为首页 ＋添加到收藏夹
如题！
有主键的情况
delete from tablename a where a.id in (select b.id from tablename b where a.id<>b.id)
蚂蚁的：去除重复值
如果有ID字段，就是具有唯一性的字段
delect table where id not in (
select max(id) from table group by col1,col2,col3...
)
group by 子句后跟的字段就是你用来判断重复的条件，如只有col1，那么只要col1字段内容相同即表示记录相同。
2,如果是判断所有字段也可以这样
select * into #aa from table group by id1,id2,....
delete table
insert into table
select * from #aa
3，没有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 子句后跟的字段就是你用来判断重复的条件，如只有col1，那么只要col1字段内容相同即表示记录相同。
2,
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...)
create table ai (i varchar(100))
insert into ai select 20030918
insert into ai select 20030918
insert into ai select 20030901
insert into ai select 20031001
insert into ai select 20031001
select i into #temps
from ai
group by i
delete from ai
insert into ai select * from #temps
drop table #temps
删除重复的，只留一条：
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
或：
select distinct * into #temp from 表
truncate table 表
insert 表 select * from #temp
drop table #temp
