declare @tab table(id int,name varchar(20))
insert @tab values(1,'aa')
insert @tab values(2,'aa')
insert @tab values(3,'bb')
insert @tab values(4,'cc')
--查看
select * from @tab
--删除
delete from @tab where name in(select name from @tab group by name
having count(name)>1)
--查看
select * from @tab
insert @tab values(1,'aa')
insert @tab values(2,'aa')
insert @tab values(3,'bb')
insert @tab values(4,'cc')
--查看
select * from @tab
--删除
delete from @tab where name in(select name from @tab group by name
having count(name)>1)
--查看
select * from @tab
truncate table 表
select identity(int,1,1) id,name into #2 from #1
insert into 表 select * from #2
drop table #1,#2
(id int,
name varchar(5))
insert t select 5,'aa'
union select 2,'bb'
union select 3,'cc'
union select 4,'dd'
select * from tselect * from t where name not in(select name from t group by name having count(id)>1)
create table t
(id int,
name varchar(5))
insert t select 5,'aa'
union select 2,'bb'
union select 3,'cc'
union select 4,'dd'select id=identity(int),name into #t from t where name not in(select name from t group by name having count(id)>1)
select * from #t