--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)
disting一下,然后存到临时表,再把源表的相关数据删掉,把临时表数据差回来。完事
select distinct * from table
没有关键字吗?如果没有,先加一个 自动ID吧。SET NOCOUNT ONcreate 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*/
--> --> (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)
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*/