# 处理重复数据的有关问题

www.MyException.Cn  网友分享于：2013-01-22  浏览：6次

[Id]   [smallint]   NOT   NULL   ,
[Card]   [int]   Not   NULL   ,
[EmployeeId]   [int]   NULL   ,
[BrushTime]   [datetime]   NOT   NULL   ,

------解决方案--------------------

[Id] [smallint] NOT NULL ,
[Card] [int] Not NULL ,
[EmployeeId] [int] NULL ,
[BrushTime] [datetime] NOT NULL ,

delete from timer where BrushTime not in (select min(BrushTime) from timer group by id)
------解决方案--------------------

select px = identity(int,1,1),* into temp from timer
delete from temp where px not in (select min(px) from temp group by id)
------解决方案--------------------
---保留相同Id,Cardid,EmployeeId,BrushTime最大那行
Delete A From Timer As A Where Exists
(Select 1 From Timer Where Id=A.Id And Cardid=A.Cardid And
EmployeeId=A.EmployeeId And BrushTime> A.BrushTime)

---保留相同Id,Cardid,EmployeeId,BrushTime最小那行
Delete A From Timer As A Where Exists
(Select 1 From Timer Where Id=A.Id And Cardid=A.Cardid And
EmployeeId=A.EmployeeId And BrushTime <A.BrushTime)

---只要Id,Cardid,EmployeeId有重复的，就将其全部删除
Delete A From @Timer As A Where Exists
(Select 1 From @Timer Where Id=A.Id And Cardid=A.Cardid And
EmployeeId=A.EmployeeId Group By Id,Cardid,EmployeeId Having Count(1)> 1)
------解决方案--------------------

------解决方案--------------------

1亿数据，天啊。这样：

select distinct * into #temp from Timer -- 但愿你的内存和磁盘空间足够

truncate table Timer

insert Timer select * from #temp

drop table #temp