删除sqlserver中的重复记录并按照条件保存其中一条

删除sqlserver中的重复记录并按照条件保存其中一条 实施代码如下： SELECT *FROM [extract].[dbo].[tbTradeFullinfoGet] WHERE tid IN(SELECT tidFROM [extract].[dbo].[tbTradeFullinfoGet]GROUP BY tidHAVING COUNT(tid) 1 ) order by tid descselect dist

删除sqlserver中的重复记录并按照条件保存其中一条
实施代码如下：

SELECT *
FROM [extract].[dbo].[tbTradeFullinfoGet] WHERE tid IN
(SELECT tid
FROM [extract].[dbo].[tbTradeFullinfoGet]
GROUP BY tid
HAVING COUNT(tid) > 1 ) order by tid desc
select distinct tid,*? into #aaa from [extract].[dbo].[tbTradeFullinfoGet]
truncate table [extract].[dbo].[tbTradeFullinfoGet]
insert [extract].[dbo].[tbTradeFullinfoGet] select * from #aaa
delete from [extract].[dbo].[tbTradeFullinfoGet]
where tid in (select tid from [extract].[dbo].[tbTradeFullinfoGet] group by tid having count(tid) > 1)
and intime not in (select max(intime) from [extract].[dbo].[tbTradeFullinfoGet] group by tid having count(tid )>1)
select a.*,
ROW_NUMBER() over (partition by a.tid order by a.intime desc) as rows_id
into?? ##test_a
from?? [extract].[dbo].[tbTradeFullinfoGet] a
delete from ##test_a
where? rows_id<>'1'
alter table ##test_a drop column rows_id
drop table [extract].[dbo].[tbTradeFullinfoGet]
select *
into?? [extract].[dbo].[tbTradeFullinfoGet]
from?? ##test_a

代码的功能是删除tbTradeFullinfoGet里重复的记录，且只保存表中intime最大的一条记录！

原文地址：删除sqlserver中的重复记录并按照条件保存其中一条, 感谢原作者分享。