一个表里有几行数据一模一样，要保留一行，删掉其余的，怎么办，求解

[quanshuiqingqing]

一个表里有几行数据一模一样，要保留一行，删掉其余的，怎么办，求解

[让你望见影子的墙]

--2、删除重复记录有大小关系时,保留大或小其中一个记录


--> --> (Roy)生成測試數據

if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
Go

--I、Name相同ID最小的记录(推荐用1,2,3),保留最小一条
方法1:
delete a from #T a where exists(select 1 from #T where Name=a.Name and ID<a.ID)

方法2:
delete a from #T a left join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID where b.Id is null

方法3:
delete a from #T a where ID not in (select min(ID) from #T where Name=a.Name)

方法4(注:ID为唯一时可用):
delete a from #T a where ID not in(select min(ID)from #T group by Name)

方法5:
delete a from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)>0

方法6:
delete a from #T a where ID<>(select top 1 ID from #T where Name=a.name order by ID)

方法7:
delete a from #T a where ID>any(select ID from #T where Name=a.Name)

[KevinLiu]

好多种方法啊

[Tragedy]

[Quote=引用 2 楼 的回复:]

好多种方法啊
[/Quote]
现在都流行垄断

[發糞塗牆]

disting一下，然后存到临时表，再把源表的相关数据删掉，把临时表数据差回来。完事

[QQ:21862245]

select distinct * from table

[小孩快跑]

我是来看贴的

[还在加载中灬]

听说是来guan水的

[ChangeMyself2012]

[Quote=引用 1 楼 的回复:]

--2、删除重复记录有大小关系时,保留大或小其中一个记录


--> --> (Roy)生成測試數據

if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
sel……
[/Quote]
学习了。

[aroc_lo]

没有关键字吗？

如果没有，先加一个 自动ID吧。

SET NOCOUNT ON

create table #temp_001(
	ID INT,
	F1 VARCHAR(10),
	F2 VARCHAR(10),
	F3 VARCHAR(10),
	F4 VARCHAR(10))
	
INSERT INTO #temp_001
SELECT 1,'A','B','C','D' UNION ALL
SELECT 2,'A','B','C','D' UNION ALL
SELECT 3,'A','B','C','D' UNION ALL
SELECT 4,'A','B','C','D' UNION ALL
SELECT 5,'A','B','C','D' UNION ALL
SELECT 6,'A','B','C','D' UNION ALL
SELECT 7,'AA','BA','CA','DA' UNION ALL
SELECT 8,'AA','BA','CA','DA' UNION ALL
SELECT 9,'AA','BA','CA','DA' UNION ALL
SELECT 10,'AA','BA','CA','DA' UNION ALL
SELECT 11,'AA','BA','CA','DA' UNION ALL
SELECT 12,'AA','BA','CA','DA' 

DELETE FROM #temp_001 
WHERE ID <>(SELECT MIN(ID) FROM #temp_001 A WHERE  A.F1=#temp_001.F1 AND A.F2=#temp_001.F2 AND A.F3=#temp_001.F3 AND A.F4 =#temp_001.F4 )


SELECT * FROM #temp_001


DROP TABLE #temp_001


/*
ID          F1         F2         F3         F4
----------- ---------- ---------- ---------- ----------
1           A          B          C          D
7           AA         BA         CA         DA

*/

[gogodiy]

最好是使用exists，可以正确使用到索引，开销小，对数据库的性能影响也小。

[tangxinzitxz]

http://topic.csdn.net/u/20121031/18/f7328c3c-2f05-4f11-a6cc-3c3143f900ff.html同时帮忙看看这个问题

[ldyhcs]

新建同结构表建立唯一索引导入

[gengjuncai]

从临时表里插入

[maysoft]

二楼正解。。。。