删除重复飞记录
zsnfksdmyxdhpcdmzyxh
4652512398291216610111006
4652512398291216610111005
4652512398291216610111003
4652512398291216610111004
4652512398291218810111038
4652512398291218810111039
4652512398291218810111040
4652512398291218810111041
4652512398291255610111029
4652512398291255610111030
4652512398291255610111031
4652512398291255610111032
4652512398301095910111023
4652512398301095910111022
4652512398301095910111020
4652512398301095910111021
4652512398301095910111024
4652512398301134110111002
4652512398301134110111003
4652512398301134110111004
4652512398301134110111005
4652512398301134110111006
4652512398301196310111012
4652512398301196310111013
4652512398301196310111011
4652512398301196310111014
4652512398301196310111015
4652512398301247210111029
4652512398301247210111030
4652512398301247710111038
4652512398301247710111039
4981314565611218810111003
4981314565611218810111002
4981314565611218810111007
4981314565611218810111004
4981314565611218810111005
4981314565611218810111006
这是一个表，我想删除有些字段相同的记录
就是当ksdm字段和yxdh字段前后的相同时，只保留一条记录。
比如说：前8个记录
zsnfksdmyxdhpcdmzyxh
4652512398291216610111006
4652512398291216610111005
4652512398291216610111003
4652512398291216610111004
4652512398291218810111038
4652512398291218810111039
4652512398291218810111040
4652512398291218810111041
我就只想保留：
zsnfksdmyxdhpcdmzyxh
4652512398291216610111006
4652512398291218810111038
就可以了（zyxh字段取什么完全不用管他）
问下sql语句怎么去写？
[最优解释]
删除重复记录有大小关系时,保留大或小其中一个记录
--> --> (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)
select * from #T
生成结果:
/*
ID Name Memo
----------- ---- ----
1 A A1
4 B B1
(2 行受影响)
*/
--II、Name相同ID保留最大的一条记录:
方法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 max(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 max(ID) from #T where Name=a.Name)
方法4(注:ID为唯一时可用):
delete a from #T a where ID not in(select max(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 desc)
方法7:
delete a from #T a where ID<any(select ID from #T where Name=a.Name)
select * from #T
/*
ID Name Memo
----------- ---- ----
3 A A3
5 B B2
(2 行受影响)
*/
--3、删除重复记录没有大小关系时，处理重复值
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([Num] int,[Name] nvarchar(1))
Insert #T
select 1,N'A' union all
select 1,N'A' union all
select 1,N'A' union all
select 2,N'B' union all
select 2,N'B'
Go
方法1:
if object_id('Tempdb..#') is not null
drop table #
Select distinct * into # from #T--排除重复记录结果集生成临时表#
truncate table #T--清空表
insert #T select * from # --把临时表#插入到表#T中
--查看结果
select * from #T
/*
Num Name
----------- ----
1 A
2 B
(2 行受影响)
*/
--重新执行测试数据后用方法2
方法2:
alter table #T add ID int identity--新增标识列
go
delete a from #T a where exists(select 1 from #T where Num=a.Num and Name=a.Name and ID>a.ID)--只保留一条记录
go
alter table #T drop column ID--删除标识列
--查看结果
select * from #T
/*
Num Name
----------- ----
1 A
2 B
(2 行受影响)
*/
--重新执行测试数据后用方法3
方法3:
declare Roy_Cursor cursor local for
select count(1)-1,Num,Name from #T group by Num,Name having count(1)>1
declare @con int,@Num int,@Name nvarchar(1)
open Roy_Cursor
fetch next from Roy_Cursor into @con,@Num,@Name
while @@Fetch_status=0
begin
set rowcount @con;
delete #T where Num=@Num and Name=@Name
set rowcount 0;
fetch next from Roy_Cursor into @con,@Num,@Name
end
close Roy_Cursor
deallocate Roy_Cursor
--查看结果
select * from #T
/*
Num Name
----------- ----
1 A
2 B
(2 行受影响)
*/
方法多哦
IF(OBJECT_ID('A')IS NOT NULL)DROP TABLE A
GO
CREATE TABLE A
(
zsnf int,
ksdm int,
yxdh int,
pcdm int,
zyxh int
)
go
insert into a
select 46525,1239829,12166,1011,1006 union all
select 46525,1239829,12166,1011,1005union all
select 46525,1239829,12166,1011,1003union all
select 46525,1239829,12166,1011,1004union all
select 46525,1239829,12188,1011,1038union all
select 46525,1239829,12188,1011,1039union all
select 46525,1239829,12188,1011,1040union all
select 46525,1239829,12188,1011,1041union all
select 46525,1239829,12556,1011,1029union all
select 46525,1239829,12556,1011,1030union all
select 46525,1239829,12556,1011,1031union all
select 46525,1239829,12556,1011,1032union all
select 46525,1239830,10959,1011,1023union all
select 46525,1239830,10959,1011,1022union all
select 46525,1239830,10959,1011,1020union all
select 46525,1239830,10959,1011,1021union all
select 46525,1239830,10959,1011,1024union all
select 46525,1239830,11341,1011,1002union all
select 46525,1239830,11341,1011,1003union all
select 46525,1239830,11341,1011,1004union all
select 46525,1239830,11341,1011,1005union all
select 46525,1239830,11341,1011,1006union all
select 46525,1239830,11963,1011,1012union all
select 46525,1239830,11963,1011,1013union all
select 46525,1239830,11963,1011,1011union all
select 46525,1239830,11963,1011,1014union all
select 46525,1239830,11963,1011,1015union all
select 46525,1239830,12472,1011,1029union all
select 46525,1239830,12472,1011,1030union all
select 46525,1239830,12477,1011,1038union all
select 46525,1239830,12477,1011,1039union all
select 49813,1456561,12188,1011,1003union all
select 49813,1456561,12188,1011,1002union all
select 49813,1456561,12188,1011,1007union all
select 49813,1456561,12188,1011,1004union all
select 49813,1456561,12188,1011,1005union all
select 49813,1456561,12188,1011,1006
select * from a
/*
zsnf ksdm yxdh pcdm zyxh
----------- ----------- ----------- ----------- -----------
46525 1239829 12166 1011 1006
46525 1239829 12166 1011 1005
46525 1239829 12166 1011 1003
46525 1239829 12166 1011 1004
46525 1239829 12188 1011 1038
46525 1239829 12188 1011 1039
46525 1239829 12188 1011 1040
46525 1239829 12188 1011 1041
46525 1239829 12556 1011 1029
46525 1239829 12556 1011 1030
46525 1239829 12556 1011 1031
46525 1239829 12556 1011 1032
46525 1239830 10959 1011 1023
46525 1239830 10959 1011 1022
46525 1239830 10959 1011 1020
46525 1239830 10959 1011 1021
46525 1239830 10959 1011 1024
46525 1239830 11341 1011 1002
46525 1239830 11341 1011 1003
46525 1239830 11341 1011 1004
46525 1239830 11341 1011 1005
46525 1239830 11341 1011 1006
46525 1239830 11963 1011 1012
46525 1239830 11963 1011 1013
46525 1239830 11963 1011 1011
46525 1239830 11963 1011 1014
46525 1239830 11963 1011 1015
46525 1239830 12472 1011 1029
46525 1239830 12472 1011 1030
46525 1239830 12477 1011 1038
46525 1239830 12477 1011 1039
49813 1456561 12188 1011 1003
49813 1456561 12188 1011 1002
49813 1456561 12188 1011 1007
49813 1456561 12188 1011 1004
49813 1456561 12188 1011 1005
49813 1456561 12188 1011 1006
(37 行受影响)
*/
--开始删除重复行操作
delete from a where zyxh not in (select max(zyxh) from a as b where a.ksdm=ksdm and a.yxdh=yxdh)
--(28 行受影响)
--结果展示
select * from a
/*
zsnf ksdm yxdh pcdm zyxh
----------- ----------- ----------- ----------- -----------
46525 1239829 12166 1011 1006
46525 1239829 12188 1011 1041
46525 1239829 12556 1011 1032
46525 1239830 10959 1011 1024
46525 1239830 11341 1011 1006
46525 1239830 11963 1011 1015
46525 1239830 12472 1011 1030
46525 1239830 12477 1011 1039
49813 1456561 12188 1011 1007
(9 行受影响)
*/
--第二种方法，重新插入数据
with x as
(
select *,row_number() over(partition by ksdm,yxdh order by zsnf,zyxh) as num
from a
)
delete from x where num <>1
--(28 行受影响)
delete a from tb a
where not exists(select 1 from tb where ksdm=a.ksdm and yxdh=a.yxdh and zyxh> a.zyxh)
DELETE a
FROM tb a
WHERE NOT EXISTS (SELECT 1
FROM tb
WHERE ksdm = a.ksdm
AND yxdh = a.yxdh
AND zyxh > a.zyxh)
insert into tb select 46525,1239829,12188,1011,1038
insert into tb select 46525,1239829,12188,1011,1039
insert into tb select 46525,1239829,12188,1011,1040
insert into tb select 46525,1239829,12188,1011,1041
go
select zsnf,ksdm,yxdh,pcdm,zyxh into tb1 from(
select *,row_number()over(partition by ksdm,yxdh order by (select 1))rn from tb
) t where rn=1
select * from tb1
drop table tb
/*
zsnf ksdm yxdh pcdm zyxh
----------- ----------- ----------- ----------- -----------
46525 1239829 12166 1011 1006
46525 1239829 12188 1011 1038
(2 行受影响)
*/
go
drop table tb1
否则,如果zyxh有相同的会删除不掉.
[其他解释]
--第三种方法
delete from a where not exists(select 1 from a as b where a.ksdm = ksdm and yxdh = a.yxdh and zyxh <a.zyxh)
