sql 中删除重复数据的方法

a.删除完全重复数据

create table #t1(SN varchar(30))

insert into #t1

   values('a'),('a'),('a'),('b'),('b'),('c')

select * from (select *,ROW_NUMBER()over(partition by sn order by sn) as seq from #t1) as aa

 

delete aa from (select *,ROW_NUMBER()over(partition by sn order by sn) as seq from #t1) as aa

where aa.seq<>1

 

select * from #t1

 

b.删除不完全重复数据

--原写法:

注：表#tmpTrace中有多笔重复记录，但是时间字段的值不同

DELETE a FROM #tmpTrace a

   WHERE EXISTS(SELECT * FROM 

          (SELECT sn,station,min(TransDateTime) AS TransDateTime FROM #tmpTrace GROUP BY sn,station HAVING count(*)>1) b

                WHERE a.sn=b.sn AND a.TransDateTime<>b.TransDateTime and a.station=b.station)

 

--使用派生表的写法:

delete aa from (

              select *,

             ROW_NUMBER()over(partition by SN,station order by TransDateTime) as Seq

              from #tmpTrace

           ) as aa

where aa.Seq<>1

 

--P.S:上面写法保留的是时间最小的数据，有些时候需要保留最新的一笔数据，这样在order by时加上DESC就可以了