删除id不等,co1,co2重复的记录:
delete from table1 as A
where id <> (select min(id) from table1 where co1 = A.co1 and co2 = A.co2)
delete from table1 as A
where id <> (select min(id) from table1 where co1 = A.co1 and co2 = A.co2)
select 字段 from table group by 字段 having count(*)>1)
)
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...)
select * from table1 a, table1 b
where a.A<> b.A and a.C=b.C
其中A为主键,C为要查找重复的字段例如下表:tablename
P_code C_code nameselect * from tablename a tablename b
where a.P_code<>b.P_code and a. C_code<>b.C_code and a.name=b.name
create table tb(col1 char(1),col2 int,col3 int,col4 int)
insert into tb
select 'A',11,20,30
union all select 'B',11,40,30
union all select 'C',20,40,30
union all select 'D',11,20,30select * from tb a
where cast(col2 as varchar)+'-'+cast(col3 as varchar)+'-'+cast(col4 as varchar)
in (select cast(col2 as varchar)+'-'+cast(col3 as varchar)+'-'+cast(col4 as varchar) from tb where a.col1<>col1)
select cast(col2 as varchar)+'-'+cast(col3 as varchar)+'-'+cast(col4 as varchar) from tb
drop table tb
drop table #temp1
GO
create table your测试表
(id1 int
)
GOselect identity(int,1,1) as mm,id1 into #temp1 from your测试表DELETE L FROM #temp1 L JOIN #temp1 R ON L.id1 = R.id1 AND L.mm > R.mm最后查询:
select id1 from #temp1