--<一>: select distinct * into #temp from 表 truncate table 表 insert 表 select * from #temp drop table --<二> 带有标识列 --备份数据 select * into #temp from 表 alter table #temp drop column id--删除原表数据 truncate table msgtable--恢复数据并去掉重复数据 insert into 表 select distinct * from #temp--<三> delete From aa where a in ( select a From aa group by a having count(a)>1) --<四> 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 --<五> --添加一个处理的标识字段 alter table 表 add id int identity(1,1) go
--删除重复记录 delete a from 表 a left join(select id=min(id) from 表 group by a,b)b on a.id=b.idwhere b.id is null go--删除处理用的标识字段 alter table 表 drop column id --参考: /* 一张表里面以两个字段为唯一字段,当几条记录的这两个字段完全相同时,需要删除重复项,如下表 a b c d 1 2 3 4 1 5 3 5 1 2 7 9 以a、b为唯一字段,第一条和第三条的a、b完全相同,所以,需要删除第一条记录1 2 3 4 或者第三条记录1 2 7 9 即如下结果: a b c d 1 2 3 4 1 5 3 5 或 a b c d 1 5 3 5 1 2 7 9
请问各位大侠这种sql语句怎么写 */
CREATE TABLE Tb1(id int, [a] varchar(255), [b] varchar(255), [c] varchar(255), [d] varchar(255)) INSERT Tb1(id, [a], [b], [c], [d]) SELECT 1, '1','2','3','4' UNION ALL SELECT 2, '1','5','3','5' UNION ALL SELECT 3, '1','2','7','9' UNION ALL SELECT 4, '1','4','7','6'delete Tb1 where [id] not in (select max([id]) from Tb1 group by a,b ) select * from tb1drop table tb1如果要同时删除第一和第三行 即如下结果: a b c d 1 5 3 5语句如下:delete m from tb t inner join ( select a ,b from tb group by a , b having count(*)>1 )n on m.a = n.a and m.b = n.b 或 delete * from tb as m, ( select a ,b from tb group by a , b having count(*)>1 )n where m.a = n.a and m.b = n.b --在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢?谢谢! 1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断 select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录 delete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)3、查找表中多余的重复记录(多个字段) select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录 delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录 select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
--先做好该表的备份,然后再。 delete tb from (select f1,f2,f3=max(f3) from tb group by f1,f2) a where tb.f1=a.f1 and tb.f2=a.f2 and tb.f3<>a.f3
--先做好该表的备份,然后再。 --或 delete tb from (select f1,f2,f3=min(f3) from tb group by f1,f2) a where tb.f1=a.f1 and tb.f2=a.f2 and tb.f3<>a.f3
select distinct f1,f2 from table1
create table tb(f1 int,f2 varchar(10),f3 varchar(10)) insert tb select 1,'china','con1' union all select 1,'china','con2' union all select 1,'china','con3' select id=identity(int,1,1),* into #1 from tbtruncate table tb insert tb select f1,f2,f3 from #1 a where not exists(select 1 from #1 where a.f1=f1 and a.f2=f2 and a.id>id)drop table #1 select * from tbf1 f2 f3 ----------- ---------- ---------- 1 china con1
select * into #a from table1TRUNCATE TABLE table1INSERT table1 SELECT f1,f2,Min(f3) as f3 FROM #a GROUP BY f1,f2
delete tb from (select f1,f2,f3=min(f3) from tb group by f1,f2) a where tb.f1=a.f1 and tb.f2=a.f2 and tb.f3<>a.f3
insert tb1 select f1,f2,f3 from tb2 a where not exists(select 1 from tb2 where a.f1=f1 and a.f2=f2 and a.id>id)
create table tb ( f1 int, f2 varchar(10), f3 varchar(10) ) delete from tb insert into tb select 1,'china','con1' union all select 1,'china','con2' union all select 1,'china','con3' union all select 2,'china2','con4' union all select 2,'china2','con5' union all select 3,'china3','con6' create function FC_StringMerge(@f1 int,@f2 varchar(10)) returns varchar(1000) as begin declare @Com varchar(1000) set @Com = '' select @Com = @Com + '或者' + f3 from tb where f1 = @f1 and f2 = @f2 return Right(@Com,len(@Com)-2) end select distinct f1,f2,dbo.FC_StringMerge(f1,f2)as M from tb /* f1 f2 M ----------- ---------- --------------------- 1 china con1或者con2或者con3 2 china2 con4或者con5 3 china3 con6 */ 这样?
delete table1 where id not in (select top 1 id from table1 table2 where table1.f1=table2.f1 and table1.f2=table2.f2)这句是凡是f1,f2重复的,只保留一条。
新建一id为唯一标示符(自增长),对表按f1进行排列,然后取重复项最小id(非重复项直接取唯一id),并以此id为条件查询,从而去除重复的数据,代码如下: select * from table1 where id in (select min(id) from table1 group by f1)
delete 表 where id not in( SELECT MAX(id) AS id FROM 表 GROUP BY rows)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
if object_id('tb') is not null drop table tb go create table tb(f1 int,f2 varchar(10),f3 varchar(10)) go insert into tb select 1,'china','con1' union all select 2,'china','con2' union all select 3,'china','con3' go delete tb from tb a where exists(select 1 from tb where f2=a.f2 and f3>a.f3)
select distinct * into #temp from 表
truncate table 表
insert 表 select * from #temp
drop table
--<二>
带有标识列
--备份数据
select * into #temp from 表
alter table #temp drop column id--删除原表数据
truncate table msgtable--恢复数据并去掉重复数据
insert into 表 select distinct * from #temp--<三>
delete From aa where a in ( select a From aa group by a having count(a)>1)
--<四>
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
--<五>
--添加一个处理的标识字段
alter table 表 add id int identity(1,1)
go
--删除重复记录
delete a
from 表 a left join(select id=min(id) from 表 group by a,b)b on a.id=b.idwhere b.id is null
go--删除处理用的标识字段
alter table 表 drop column id
--参考:
/*
一张表里面以两个字段为唯一字段,当几条记录的这两个字段完全相同时,需要删除重复项,如下表
a b c d
1 2 3 4
1 5 3 5
1 2 7 9
以a、b为唯一字段,第一条和第三条的a、b完全相同,所以,需要删除第一条记录1 2 3 4 或者第三条记录1 2 7 9
即如下结果:
a b c d
1 2 3 4
1 5 3 5
或
a b c d
1 5 3 5
1 2 7 9
请问各位大侠这种sql语句怎么写 */
CREATE TABLE Tb1(id int, [a] varchar(255), [b] varchar(255), [c] varchar(255), [d] varchar(255))
INSERT Tb1(id, [a], [b], [c], [d])
SELECT 1, '1','2','3','4'
UNION ALL SELECT 2, '1','5','3','5'
UNION ALL SELECT 3, '1','2','7','9'
UNION ALL SELECT 4, '1','4','7','6'delete Tb1 where [id] not in (select max([id]) from Tb1 group by a,b )
select * from tb1drop table tb1如果要同时删除第一和第三行
即如下结果:
a b c d
1 5 3 5语句如下:delete m from tb t
inner join
(
select a ,b
from tb
group by a , b
having count(*)>1
)n
on m.a = n.a and m.b = n.b
或
delete * from tb as m,
(
select a ,b
from tb
group by a , b
having count(*)>1
)n
where m.a = n.a and m.b = n.b
--在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢?谢谢!
1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
delete from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)3、查找表中多余的重复记录(多个字段)
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
delete tb from
(select f1,f2,f3=max(f3) from tb group by f1,f2) a where tb.f1=a.f1 and tb.f2=a.f2 and tb.f3<>a.f3
--或
delete tb from
(select f1,f2,f3=min(f3) from tb group by f1,f2) a where tb.f1=a.f1 and tb.f2=a.f2 and tb.f3<>a.f3
insert tb
select 1,'china','con1' union all
select 1,'china','con2' union all
select 1,'china','con3' select id=identity(int,1,1),*
into #1
from tbtruncate table tb
insert tb
select f1,f2,f3
from #1 a
where not exists(select 1 from #1 where a.f1=f1 and a.f2=f2 and a.id>id)drop table #1
select * from tbf1 f2 f3
----------- ---------- ----------
1 china con1
select * into #a from table1TRUNCATE TABLE table1INSERT table1
SELECT f1,f2,Min(f3) as f3 FROM #a GROUP BY f1,f2
(select f1,f2,f3=min(f3) from tb group by f1,f2) a where tb.f1=a.f1 and tb.f2=a.f2 and tb.f3<>a.f3
select f1,f2,f3
from tb2 a
where not exists(select 1 from tb2 where a.f1=f1 and a.f2=f2 and a.id>id)
(
f1 int,
f2 varchar(10),
f3 varchar(10)
)
delete from tb
insert into tb select 1,'china','con1' union all
select 1,'china','con2' union all
select 1,'china','con3' union all
select 2,'china2','con4' union all
select 2,'china2','con5' union all
select 3,'china3','con6' create function FC_StringMerge(@f1 int,@f2 varchar(10))
returns varchar(1000)
as
begin
declare @Com varchar(1000)
set @Com = ''
select @Com = @Com + '或者' + f3 from tb where f1 = @f1 and f2 = @f2
return Right(@Com,len(@Com)-2)
end
select distinct f1,f2,dbo.FC_StringMerge(f1,f2)as M from tb
/*
f1 f2 M
----------- ---------- ---------------------
1 china con1或者con2或者con3
2 china2 con4或者con5
3 china3 con6
*/
这样?
select * from table1 where id in (select min(id) from table1 group by f1)
SELECT MAX(id) AS id FROM 表 GROUP BY rows)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
if object_id('tb') is not null
drop table tb
go
create table tb(f1 int,f2 varchar(10),f3 varchar(10))
go
insert into tb
select 1,'china','con1' union all
select 2,'china','con2' union all
select 3,'china','con3'
go
delete tb from tb a where exists(select 1 from tb where f2=a.f2 and f3>a.f3)