首页 > 数据库 > mysql教程 > 删除sqlserver中的重复记录并按照条件保存其中一条

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
发布: 2016-06-07 16:37:34
原创
1110 人浏览过

删除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最大的一条记录!

来源:php.cn
本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板