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 #temp where id in (
select max(id) from #emp where having count(*)>1 group by col1,col2,col3...)
你可以这样,选择你要的那条记录,把他保存到新表,然后再删除旧表。
1:delete from member where username not in(selectselect distinct username from member)
2:delete from member where username not in(selectselect top 1 username from member )
1.以这个表A生成另一个完全一样的表B.
2.delete a
3.insert into a (select max(col001) as col001,max(col002) as col002,.....from b group by username)
4.删掉b
)
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...)
from 表 a where 主键<>(select top 1 主键 where username=a.username)
--1.先增加标识字段
alter table 表 add id int identity(1,1)
go--再删除
delete 表
from 表 a where id<>(select top 1 id where username=a.username)
go--处理完成后删除标识字段
alter table 表 drop column id
from 表 a where 主键<>(select top 1 主键 from 表 where username=a.username)
create table 表(主键 int primary key,username varchar(10),info varchar(10))
insert into 表
select 1,'张三','a'
union all select 2,'张三','b'
union all select 3,'张三','c'
union all select 4,'李四','d'
union all select 5,'张三','e'
union all select 6,'李四','f'
go--删除处理
delete 表
from 表 a where 主键<>(select top 1 主键 from 表 where username=a.username)
go--显示处理结果
select * from 表
go--删除测试环境
drop table 表/*--测试结果
主键 username info
----------- ---------- ----------
1 张三 a
4 李四 d(所影响的行数为 2 行)
--*/
alter table 表 add id int identity(1,1)
go--再删除
delete 表
from 表 a where id<>(select top 1 id from 表 where username=a.username)
go--处理完成后删除标识字段
alter table 表 drop column id