不幸的是,我在某个时刻陷入了困境,无法继续前进。让我描述一下问题出在哪里。
简短描述:我写了一个系统,不断检查服务器的状态,结果是1或0(真或假(。这些状态正在写入名为"logs"的数据库表中。有多个监控系统。现在,我想要实现的是从数据库中获取每个系统的状态更改列表。(仅当ex.的状态从联机变为脱机时(所有数据段。
结构:
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
基于上面的示例数据,我想创建SQL查询(MySQL数据库(,它将显示以下结果-
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_rid==5的状态,因此只应返回此状态。所以我将得到四行结果。
提前感谢你的帮助,我已经筋疲力尽了。问候,迈克尔。
如果你运行的是MySQL 8.0,你可以使用lag()
:
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
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
这是基于@GMB解决方案的最终版本,再次感谢@GMB。