select min(id) as id ,name INTO #a from TB group by name having count(*)>1delete tb from tb a,#a b where a.id=b.id
创建临时表#t create table #t (id int identity(1,1), name varchar(30) not null) 插入数据: id name 1 A 2 A 3 A 4 A 5 B 6 B 7 B 8 C 9 C 10 C 11 C delete from #t where id in(select min(id) from #t group by name) select * from #t 结果: id name 2 A 3 A 4 A 6 B 7 B 9 C 10 C 11 C
delete T from T a where a.id = (select min(id) from T where NAME = a.NAME) and (select count(*) from T where NAME = a.NAME) > 1
如果ID在表中是唯一的值字段时: delete from tb_name where id in ( select id=min(id) from tb_name group by name having count(name)>1 ) 如果ID在表中不是唯一的值字段时: select id,name into #t from tb_name group by name having count(name)>1 delete tb_name from tb_name a,#t b where a.id=b.id and a.name=b.=name
delete select * from Table group by 号码 Having Count(*)>1
delete from tb_name as TA where not exists ( select 1 from tb_name where TA.[name]=[name] and TA.id<id) )
delete from 表 where id in (select id=min(id) from 表 group by name having count(*)>1)
delete from invest where invest_id=(select min(invest_id) from invest group by invest_name having count(*)>1)
delete temp1 where id in ( select min(id) as id from temp1 group by name having count(*)>1 )
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[temp1]create table temp1(id int not null primary key,name varchar(10) null) goinsert into temp1 select 1,'aaa' union select 2,'bbb' union select 3,'ccc' union select 4,'aaa' union select 5,'ddd' union select 6,'ccc' goselect * from temp1 /* id name 1 aaa 2 bbb 3 ccc 4 aaa 5 ddd 6 ccc*/delete temp1 where id in ( select min(id) as id from temp1 group by name having count(*)>1 )select * from temp1 /* id name 2 bbb 4 aaa 5 ddd 6 ccc*/
--id在數據庫中唯一 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[temp1]create table temp1(id int not null primary key,name varchar(10) null) goinsert into temp1 select 1,'aaa' union select 2,'bbb' union select 3,'ccc' union select 4,'aaa' union select 5,'ddd' union select 6,'ccc' goselect * from temp1 /* id name 1 aaa 2 bbb 3 ccc 4 aaa 5 ddd 6 ccc*/delete temp1 where id in ( select min(id) as id from temp1 group by name having count(*)>1 )select * from temp1 /* id name 2 bbb 4 aaa 5 ddd 6 ccc*/
group by name
having count(*)>1delete tb from tb a,#a b where a.id=b.id
create table #t
(id int identity(1,1),
name varchar(30) not null)
插入数据:
id name
1 A
2 A
3 A
4 A
5 B
6 B
7 B
8 C
9 C
10 C
11 C
delete from #t
where id in(select min(id) from #t group by name)
select * from #t
结果:
id name
2 A
3 A
4 A
6 B
7 B
9 C
10 C
11 C
from T a
where a.id = (select min(id) from T where NAME = a.NAME)
and
(select count(*) from T where NAME = a.NAME) > 1
delete from tb_name where id in
(
select id=min(id) from tb_name group by name having count(name)>1
)
如果ID在表中不是唯一的值字段时:
select id,name into #t from tb_name group by name having count(name)>1
delete tb_name from tb_name a,#t b where a.id=b.id and a.name=b.=name
select *
from Table
group by 号码
Having Count(*)>1
(
select 1 from tb_name where TA.[name]=[name] and TA.id<id)
)
(select id=min(id)
from 表
group by name having count(*)>1)
(
select min(id) as id from temp1 group by name having count(*)>1
)
drop table [dbo].[temp1]create table temp1(id int not null primary key,name varchar(10) null)
goinsert into temp1
select 1,'aaa' union
select 2,'bbb' union
select 3,'ccc' union
select 4,'aaa' union
select 5,'ddd' union
select 6,'ccc'
goselect * from temp1
/*
id name
1 aaa
2 bbb
3 ccc
4 aaa
5 ddd
6 ccc*/delete temp1 where id in
(
select min(id) as id from temp1 group by name having count(*)>1
)select * from temp1
/*
id name
2 bbb
4 aaa
5 ddd
6 ccc*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[temp1]create table temp1(id int not null primary key,name varchar(10) null)
goinsert into temp1
select 1,'aaa' union
select 2,'bbb' union
select 3,'ccc' union
select 4,'aaa' union
select 5,'ddd' union
select 6,'ccc'
goselect * from temp1
/*
id name
1 aaa
2 bbb
3 ccc
4 aaa
5 ddd
6 ccc*/delete temp1 where id in
(
select min(id) as id from temp1 group by name having count(*)>1
)select * from temp1
/*
id name
2 bbb
4 aaa
5 ddd
6 ccc*/