作者：潘泓浩_236 | 来源：互联网 | 2023-05-18 06:53
我有一张表，里面有6个字段，分别是a1,a2,a3,a4,a5,a6，我想根据a1,a2,a3这三个字段来剔除重复的数据，只要这3个字段的值相等，我就剔除掉重复的，应该怎么做？谢谢，各位大侠。
我有一张表，里面有6个字段，分别是a1,a2,a3,a4,a5,a6，我想根据a1,a2,a3这三个字段来剔除重复的数据，只要这3个字段的值相等，我就剔除掉重复的，应该怎么做？谢谢，各位大侠。
8 个解决方案
DELETE FROM table WHERE a1=a2 and a2=a3
select a1,a2,a3,min(a4) as a4,min(a5) as a5,min(a6) as a6 into #Temp
from [TableName]
group by a1,a2,a3
having count(*)>1
delete t1
from [TableName] t1,#Temp t2
where t1.a1=t2.a1 and t1.a2=t2.a2 and t1.a3=t2.a3
insert into [TableName]
select * from #Temp
drop table #Temp
好了，给分吧
create table #tmpTbl
(id INT, a1 int, a2 int, a3 int, a4 int)
insert into #tmpTbl
select 1,1,1,2,3
union all select 2,1,1,2,4
union all select 3,1,2,2,4
union all select 4,1,2,2,4
select * from #tmpTbl
delete from #tmpTbl where id not in(
select MIN(id) from #tmpTbl
group by a1,a2,a3)
select * from #tmpTbl
drop table #tmpTbl
delete from test where al=a2 and a2=a3
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
select a1,a2,a3,min(a4) as a4,min(a5) as a5,min(a6) as a6 into #Temp
from [TableName]
group by a1,a2,a3
having count(*)>1
delete t1
from [TableName] t1,#Temp t2
where t1.a1=t2.a1 and t1.a2=t2.a2 and t1.a3=t2.a3
insert into [TableName]
select * from #Temp
drop table #Temp
京公网安备 11010802041100号