蚂蚁的:去除重复值 如果有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 #aa3,没有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 idelete from aiinsert into ai select * from #tempsdrop 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
如果有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 #aa3,没有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 idelete from aiinsert into ai select * from #tempsdrop 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