一个比较难一点的删除重复记录

Question

WHERE (JobID = '5') AND (FlashTime BETWEEN '2003-11-05 07:30:00' AND 
      '2003-11-06 07:30:00')
ORDER BY FlashTime DESC

Answer 1

SELECT *
FROM HFlashCardRecord
WHERE (JobID = '5') AND (FlashTime BETWEEN '2003-11-05 07:30:00' AND 
      '2003-11-06 07:30:00')
ORDER BY FlashTime DESC

Answer 2

SELECT *
FROM HFlashCardRecord
WHERE (JobID = '5') AND (FlashTime BETWEEN '2003-11-05 07:30:00' AND 
      '2003-11-06 07:30:00')
ORDER BY FlashTime
出来的记录可以当成一个表。

Answer 3

to 超級學生 用循环，我以前是用的这种游标，应该可以用SQL语句的方法的。既然可以删除重复的记录,也应该可以删除两个时间相差在20分钟以内的数据

Answer 4

DECLARE
@DtStart DateTime,@DtEnd Datetime
SELECT @DtStart='2003-11-05 07:30:00'
SELECT @DtEnd='2003-11-06 07:30:00'IF (SELECT object_ID('tempdb.dbo.#Flash'))>0 DROP TABLE #Flash
SELECT identity(int,1,1) as [ID],* 
INTO #Flash
FROM HFlashCardRecord 
Where JobID='5' AND FlashTime Between @DtStart AND @DtEnd
ORDER BY FlashTimeSELECT * FROM #FlashSELECT * FROM #Flash WHERE #Flash.[ID]!= 
(SELECT MAX([ID]) FROM #Flash a where (dbo.TimeMargin(#Flash.[FlashTime],a.[FlashTime])=0))得到的结果是这样的（所影响的行数为 7 行）ID          JobID                     SalaryNO                  FlashTime                                              ClockNO Mark 
----------- ------------------------- ------------------------- ------------------------------------------------------ ------- ---- 
1           5                         0000737009                2003-11-05 07:54:04.000                                01      1
2           5                         0000737009                2003-11-05 12:04:28.000                                02      1
3           5                         000737009                 2003-11-05 12:04:30.000                                02      1
4           5                         0000737009                2003-11-05 12:23:01.000                                01      1
5           5                         0000737009                2003-11-05 17:32:54.000                                02      1
6           5                         0000737009                2003-11-05 18:09:08.000                                01      1
7           5                         0000737009                2003-11-05 20:32:45.000                                02      1（所影响的行数为 7 行）ID          JobID                     SalaryNO                  FlashTime                                              ClockNO Mark 
----------- ------------------------- ------------------------- ------------------------------------------------------ ------- ---- 
2           5                         0000737009                2003-11-05 12:04:28.000                                02      1（所影响的行数为 1 行）

Answer 5

（所影响的行数为 7 行）ID  JobID      SalaryNO         FlashTime                 ClockNO Mark 
------------------------------- ------------------------------------
1   5        0000737009       2003-11-05 07:54:04.000   01      1
2   5        0000737009       2003-11-05 12:04:28.000   02      1
3   5        000737009        2003-11-05 12:04:30.000   02      1
4   5        0000737009       2003-11-05 12:23:01.000   01      1
5   5        0000737009       2003-11-05 17:32:54.000   02      1
6   5        0000737009       2003-11-05 18:09:08.000   01      1
7   5        0000737009       2003-11-05 20:32:45.000   02      1（所影响的行数为 7 行）ID  JobID    SalaryNO         FlashTime                 ClockNO Mark 
------------------------------- --------------------------------------
2   5       0000737009       2003-11-05 12:04:28.000   02      1（所影响的行数为 1 行）

Answer 6

/****** Object:  Table [dbo].[HFlashCardRecord]    Script Date: 2003-12-27 9:38:16 ******/
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HFlashCardRecord]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
CREATE TABLE [dbo].[HFlashCardRecord] (
[JobID] [varchar] (25) COLLATE Chinese_PRC_CI_AS NULL ,
[SalaryNO] [varchar] (25) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[FlashTime] [datetime] NOT NULL ,
[ClockNO] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[Mark] [tinyint] NULL CONSTRAINT [DF_HFlashCardRecord_Mark] DEFAULT (0),
CONSTRAINT [PK_HFlashCardRecord] PRIMARY KEY  CLUSTERED 
(
[SalaryNO],
[FlashTime]
)  ON [PRIMARY] 
) ON [PRIMARY]
 CREATE  INDEX [IX_HFlashCardRecord] ON [dbo].[HFlashCardRecord]([JobID], [FlashTime]) ON [PRIMARY]
ENDGOINSERT INTO HFlashCardRecord VALUES('5','0000737009','2003-11-05 07:54:04.000','01',1)
INSERT INTO HFlashCardRecord VALUES('5','0000737009','2003-11-05 12:04:28.000','02',1)
INSERT INTO HFlashCardRecord VALUES('5','0000737009','2003-11-05 12:04:30.000','01',1)
INSERT INTO HFlashCardRecord VALUES('5','0000737009','2003-11-05 12:23:01.000','02',1)
INSERT INTO HFlashCardRecord VALUES('5','0000737009','2003-11-05 17:32:54.000','01',1)
INSERT INTO HFlashCardRecord VALUES('5','0000737009','2003-11-05 18:09:08.000','02',1)
INSERT INTO HFlashCardRecord VALUES('5','0000737009','2003-11-05 20:32:45.000','02',1)
表结构如上，粘贴一下运行就行了意思是FlashTime在20分钟以内的删除最后一次
像打卡一样如果在20分钟内打了两次卡,删除最后一次

Answer 7

太简单
delete from [dbo].[HFlashCardRecord] h1 where 
exists (select 1 from [dbo].[HFlashCardRecord] h2 where 
h2.JobID =h.JobID and h2.SalaryNO=h. SalaryNO and convert(float,h1.FlashTime-h2.FlashTime) between 0 and 1/24/60*20)

Answer 8

如果有进一步要求，比如 每20分钟为一个段，参考：
http://expert.csdn.net/Expert/TopicView1.asp?id=2553304

Answer 9

realgz
还是不行，不信你帮我试试，我的表结构和数据都在那里呢

Answer 10

换了一个写法：
delete h1
from  [HFlashCardRecord] h1, [HFlashCardRecord] h2
where 
     h2.JobID =h1.JobID 
     and h2.SalaryNO=h1.SalaryNO 
     and h2.FlashTime <h1. FlashTime 
     and convert(float,h1.FlashTime-h2.FlashTime) between 0 and (1.000000/24/60)*20

Answer 11

declare @LineIdStart int,@LineIdOver int,@saveSalaryno varchar(10) ---一個用來記錄起始id,最大id
declare @TempLineIdStart int,@TempLineIdOver int
declare @onedatetime datetime,@twodatetime datetime
Set @LineIdStart=1select distinct(salaryno) into #t2 from bb ---取得所有id/ic卡號
select identity(int,1,1) as Lineid,* into #t from #t2   --連結成表
Drop table #t2select @LineIdOver=Max(LineID) from #t ---取得總人數While @LineIdStart<=@LineIdOver
     begin
Select @SaveSalaryno=salaryno from #t where Lineid=@lineidstart ---取得每個員工的編號
Select Identity(int,1,1) as LineId,* into #t1 from bb where Salaryno=@SaveSalaryno--取得每個員工的刷卡記錄 set @TempLineidstart=1
Select @TempLineidOver=Max(Lineid) from #t1 while @templineidstart<=@templineidover
     begin
Select * into #t3 from #t1 where Lineid between @templineidstart and @templineidstart+1
select top 1 @onedatetime=FlashTime from #t3 order by lineid asc
select top 1 @twodatetime=flashtime from #t3 order by lineid desc
select @onedatetime,@twodatetime
select datediff(n,@onedatetime,@twodatetime)
if datediff(n,@onedatetime,@twodatetime)<=20 and @onedatetime<>@twodatetime
begin
select @savesalaryno,@twodatetime
      delete from bb where salaryno=@SaveSalaryno and flashtime=@twodatetime
end 
Set @templineidstart=@templineidstart+1
Drop table #t3
     end
Drop table #t1
Set @LineIdStart=@LineIdstart+1
      end
drop table #t