再求一句mysql语句，删除间隔时间小于2秒的记录

Walkline 2017-10-09 05:24:43

还是一张recorders表，假设。。。。。就两个字段type和date，date不是时间戳，不过也是类似时间戳的东西，数值，可以比较大小

这是一个刷卡记录的项目，客户需求是刷一次，代表开启设备A，连刷两次代表开启设备B

情况一：刷一次，刷卡设备会保存一条正常刷卡记录
情况二：刷两次，会保存两条刷卡记录，第一条也是正常记录，第二条是刷卡间隔太短的记录

对于情况一就直接记录直接使用就行，代表开启设备A，type=0
对于情况二，需要取的是第二条，也就是间隔太短这条记录，代表开启设备B，type=1

但是情况二保存的第一条记录会被误认为开启了设备A，然后紧接着第二条记录又代表开启了设备B，也就是同时开启了设备A和设备B，为了避免这种情况，我需要一条delete语句，如果两条相邻的记录其它字段内容一致（不包括type），但是date字段数值差小于等于2，就把type=0或者date数值较小的那条记录删除，最后只保留间隔太短的那条记录



id    cardnumber  pin      type  date  

10	07007716	18		0	570129508

11	07007716	18		1	570129509

也就是把id=10的删掉，保留id=11的记录

...全文

480 13 打赏收藏转发到动态举报

写回复

用AI写文章

13 条回复

切换为时间正序

请发表友善的回复…

发表回复

Walkline 2017-10-11

打赏
举报

引用 12 楼 zjcxc 的回复:

大概就这个意思

start transaction;
update recorders as a
    set a.EventType = {0}, a.Date = {1} 
where
    a.PIN = {2}
    and a.CardNumber = {3}
    and a.ClassName = {4}
    and ({5} - a.Date) <= 2
    and (
        a.EventType = 0
        or a.EventType = 20
    )
;
insert recorders select * from( select {0} as EventType,  {1} as Date ) x where row_count() = 0;
commit;

长见识了。。。。。结贴

Walkline 2017-10-10

打赏
举报

引用 3 楼 qq_37170555 的回复:

建议多给出一些测试数据，你这就两条数据，按你说的。我自己的理解那不就是直接删除时间小的那条了啊，还用搞得这么复杂啊。。所以建议多给些数据，要保留哪些，要删除哪些，标注出来。这样大家才看得清楚明白，知道你需要做什么

我应该丢10w条没排过版的记录给你

Walkline 2017-10-10

打赏
举报

引用 4 楼 rucypli 的回复:

要删除的id select A.id from tb A,tb B where A.id=B.id-1 and A.carnumber=B.carnumber and A.pin=B.pin and A.date=B.date-2 and A.type<>B.type


记录
id  cardnumber  pin  eventtype  date  
1	00095367	19	0	567907199
2	06034084	0	27	570036777
3	07332724	0	27	570036783
4	07272196	0	27	570036787
5	07007716	18	0	570038464
6	05131445	17	0	570038467
7	00095367	19	0	570044486
8	07007716	18	0	570044511
9	00000000	0	206	570115150
10   07007716	18	0	570129508
11   07007716	18	20	570129509
12   07007716	18	20	570129510
13   07007716	18	0	570129514


查询
SELECT
	a.ID,
	a.CardNumber,
	a.PIN,
	a.ClassName,
	a.VerifyType,
	a.DoorId,
	a.EventType,
	a.State,
	a.Date
FROM
	recorders AS a,
	recorders AS b
WHERE
	a.ID = b.ID - 1
AND a.PIN = b.PIN
AND a.Date - b.Date <= 2
AND (
	a.EventType = 0
)


结果
id  cardnumber  pin  eventtype  date
10	07007716	18	0	570129508

目前测试数据比较少，不过现在这样看差不多是对的了

Walkline 2017-10-10

打赏
举报

引用 2 楼 zjcxc 的回复:

连续刷新卡2分钟，每条记录的时间间隔都小于 2秒又是什么情况？你如何界定应该保留那条？如果仅仅是保留最后一条，那么你又如何确定开启的是设备 B？因为按照你的最终数据，2秒内的只保留一条，而这种只有一次数据是开户设备A 所以个人觉得这个思路不对个人觉得你这个应该是两个时间，开始时间和结束时间 + 刷卡次数首次刷新，开始时间=结束时间，刷新次数=1 后续次刷新，更新 2 秒内的那条，结束时间为当前时间，刷卡次数+1, 如果更新不到记录，则做 insert （首次刷刷卡）

刷卡记录不管什么状态，在刷卡设备上都会生成一条记录，我想的比较简单，拉取刷卡记录就简单的拉取，保存到数据库里再去分析并删除，这样可以省去很多逻辑。。。。。稍后试一下吧。。。。。。。

zjcxc 2017-10-10

打赏
举报

大概就这个意思

start transaction;
update recorders as a
    set a.EventType = {0}, a.Date = {1} 
where
    a.PIN = {2}
    and a.CardNumber = {3}
    and a.ClassName = {4}
    and ({5} - a.Date) <= 2
    and (
        a.EventType = 0
        or a.EventType = 20
    )
;
insert recorders select * from( select {0} as EventType,  {1} as Date ) x where row_count() = 0;
commit;

Walkline 2017-10-10

打赏
举报

引用 10 楼 zjcxc 的回复:

[quote=引用 9 楼 Walkline 的回复:] [quote=引用 8 楼 ACMAIN_CHM 的回复:] 用游标或者程序实现吧。既然有10万条记录。

10万条。。。。。。以客户现在的规模10万条记录够用400年了


update recorders as a
    set a.EventType = {0}, a.Date = {1} 
where
    a.PIN = {2}
    and a.CardNumber = {3}
    and a.ClassName = {4}
    and ({5} - a.Date) <= 2
    and (
        a.EventType = 0
        or a.EventType = 20
    )

@zjcxc 最后是这样处理的，在插入记录之前先update，根据影响行数决定是否再insert，这应该是你的意思吧？目前不知道是手残还是怎么滴，好像还有update错误的记录，但是又好像没有，只能祈祷这段sql是万分正确的吧[/quote] updae 和 insert 放在一个事务中，这样才能确保并发不会导致重复数据[/quote] 那意思是还用insert into on duplicate key update？不过这里可没有唯一索引了，除了id没有可以唯一的字段另，一个事务啥意思？

zjcxc 2017-10-10

打赏
举报

引用 9 楼 Walkline 的回复:

[quote=引用 8 楼 ACMAIN_CHM 的回复:] 用游标或者程序实现吧。既然有10万条记录。

10万条。。。。。。以客户现在的规模10万条记录够用400年了


update recorders as a
    set a.EventType = {0}, a.Date = {1} 
where
    a.PIN = {2}
    and a.CardNumber = {3}
    and a.ClassName = {4}
    and ({5} - a.Date) <= 2
    and (
        a.EventType = 0
        or a.EventType = 20
    )

Walkline 2017-10-10

打赏
举报

引用 8 楼 ACMAIN_CHM 的回复:

用游标或者程序实现吧。既然有10万条记录。

10万条。。。。。。以客户现在的规模10万条记录够用400年了


update recorders as a
    set a.EventType = {0}, a.Date = {1} 
where
    a.PIN = {2}
    and a.CardNumber = {3}
    and a.ClassName = {4}
    and ({5} - a.Date) <= 2
    and (
        a.EventType = 0
        or a.EventType = 20
    )

@zjcxc 最后是这样处理的，在插入记录之前先update，根据影响行数决定是否再insert，这应该是你的意思吧？目前不知道是手残还是怎么滴，好像还有update错误的记录，但是又好像没有，只能祈祷这段sql是万分正确的吧

ACMAIN_CHM 2017-10-10

打赏
举报

用游标或者程序实现吧。既然有10万条记录。

rucypli 2017-10-09

打赏
举报

要删除的id select A.id from tb A,tb B where A.id=B.id-1 and A.carnumber=B.carnumber and A.pin=B.pin and A.date=B.date-2 and A.type<>B.type

听雨停了 2017-10-09

打赏
举报

建议多给出一些测试数据，你这就两条数据，按你说的。我自己的理解那不就是直接删除时间小的那条了啊，还用搞得这么复杂啊。

。所以建议多给些数据，要保留哪些，要删除哪些，标注出来。这样大家才看得清楚明白，知道你需要做什么

zjcxc 2017-10-09

打赏
举报

连续刷新卡2分钟，每条记录的时间间隔都小于 2秒又是什么情况？你如何界定应该保留那条？如果仅仅是保留最后一条，那么你又如何确定开启的是设备 B？因为按照你的最终数据，2秒内的只保留一条，而这种只有一次数据是开户设备A 所以个人觉得这个思路不对个人觉得你这个应该是两个时间，开始时间和结束时间 + 刷卡次数首次刷新，开始时间=结束时间，刷新次数=1 后续次刷新，更新 2 秒内的那条，结束时间为当前时间，刷卡次数+1, 如果更新不到记录，则做 insert （首次刷刷卡）

Walkline 2017-10-09