MySQL记录表查询以获取更改的数据

不幸的是,我被困在某个点上,无法前进。我来描述一下有什么问题。

结构:

Table [logs] -
logs.logs_id == mon.mon_id
logs.logs_status 
logs.logs_updated

Table [monitoring] -
mon.mon_id
mon.mon_sid
mon.....

示例数据-

logs.logs_id | logs.logs_status | logs.logs_updated
1    1    2020/02/29 21:04:00
2    1    2020/02/29 21:04:00
3    1    2020/02/29 21:04:00
4    1    2020/02/29 21:04:00
5    1    2020/02/29 21:04:00
6    0    2020/02/29 21:04:00
1    1    2020/02/29 21:04:10
2    1    2020/02/29 21:04:10
3    1    2020/02/29 21:04:10
4    1    2020/02/29 21:04:10
5    1    2020/02/29 21:04:10
6    0    2020/02/29 21:04:10
1    1    2020/02/29 21:04:20
2    1    2020/02/29 21:04:20
3    1    2020/02/29 21:04:20
4    1    2020/02/29 21:04:20
5    1    2020/02/29 21:04:20
6    0    2020/02/29 21:04:20
1    1    2020/02/29 21:04:30
2    1    2020/02/29 21:04:30
3    1    2020/02/29 21:04:30
4    1    2020/02/29 21:04:30
5    1    2020/02/29 21:04:30
6    1    2020/02/29 21:04:30
1    1    2020/02/29 21:04:40
2    1    2020/02/29 21:04:40
3    1    2020/02/29 21:04:40
4    1    2020/02/29 21:04:40
5    1    2020/02/29 21:04:40
6    1    2020/02/29 21:04:40
1    1    2020/02/29 21:04:50
2    1    2020/02/29 21:04:50
3    1    2020/02/29 21:04:50
4    1    2020/02/29 21:04:50
5    1    2020/02/29 21:04:50
6    0    2020/02/29 21:04:50
1    1    2020/02/29 21:05:00
2    1    2020/02/29 21:05:00
3    1    2020/02/29 21:05:00
4    1    2020/02/29 21:05:00
5    0    2020/02/29 21:05:00
6    0    2020/02/29 21:05:00

mon.mon_sid | logs.logs_status | logs.logs_updated
YYY        0    2020/02/29 21:05:00
XXX        0    2020/02/29 21:04:50
XXX        1    2020/02/29 21:04:30
XXX        0    2020/02/29 21:04:00

由于根据数据只更改了log.mon_id==6和log.mon_id==5的状态,因此只应返回此状态。所以我有四行结果。

事先谢谢你的帮助,我很累想弄清楚。

select mon.mon_sid, mon.mon_hostname, mon.mon_port, mon.mon_display, logs_status AS current_status, previous_status, logs_updated from ( select l.*, lag(logs_status) over( partition by mon_id order by logs_updated ) previous_status from logs l ) tmp INNER JOIN monitoring AS mon ON tmp.mon_id = mon.mon_id where logs_status <> previous_status ORDER BY logs_updated DESC

select * from ( select l.*, lag(logs_status) over( partition by logs_id order by logs_updated ) lag_log_status from logs l ) t where log_status <> lag_log_status