删除sql表中的重复项
--测试数据
/*-----------------------------
select * from test-----------------------------*/
id pid
----------- -----------
1 1
1 1
2 2
3 3
3 3
3 3
(所影响的行数为 6 行)
首先,如何查询table中有重复记录
select *,count(1) as rownum
from test
group by id, pid(注:写该项查询语句时注意要把表中的所有字段都写出来,不能单单写一两项。)
having count(1) > 1
id pid rownum
----------- ----------- -----------
1 1 2
3 3 3
(所影响的行数为 2 行)
方法一:使用distinct和临时表
if object_id(tempdb..#tmp) is not null
drop table #tmp
select distinct * into #tmp from test
truncate table test
insert into test select * from #tmp
方法二:添加标识列 (推荐使用该方法)
alter table testadd newid int identity(1,1)
go
delete from test where exists(select 1 from testa where a.newid>test.newid and test.id=a.id and test.pid=a.pid)
go
alter table testdrop column newid
go
--测试结果
/*-----------------------------
select * from test
-----------------------------*/
id pid
----------- -----------
1 1
2 2
3 3
(所影响的行数为 3 行)
--测试数据
/*-----------------------------
select * from test-----------------------------*/
id pid
----------- -----------
1 1
1 1
2 2
3 3
3 3
3 3
(所影响的行数为 6 行)
首先,如何查询table中有重复记录
select *,count(1) as rownum
from test
group by id, pid(注:写该项查询语句时注意要把表中的所有字段都写出来,不能单单写一两项。)
having count(1) > 1
id pid rownum
----------- ----------- -----------
1 1 2
3 3 3
(所影响的行数为 2 行)
方法一:使用distinct和临时表
if object_id(tempdb..#tmp) is not null
drop table #tmp
select distinct * into #tmp from test
truncate table test
insert into test select * from #tmp
方法二:添加标识列 (推荐使用该方法)
alter table testadd newid int identity(1,1)
go
delete from test where exists(select 1 from testa where a.newid>test.newid and test.id=a.id and test.pid=a.pid)
go
alter table testdrop column newid
go
--测试结果
/*-----------------------------
select * from test
-----------------------------*/
id pid
----------- -----------
1 1
2 2
3 3
(所影响的行数为 3 行)