表字段如下:
表记录了每个服务(server_id)的不同节点(node_id)的不同时间(create_time)的状态记录
CREATE TABLE public.server_status (
id varchar(255) NOT NULL,
creat_time timestamp NULL,
is_online varchar(255) NOT NULL,
msg varchar(255) NULL,
node_id varchar(255) NOT NULL,
online_update_time timestamp NULL,
server_id varchar(255) NOT NULL,
server_level varchar(255) NULL,
user_identify varchar(255) NOT NULL,
CONSTRAINT xviid_server_status_pkey PRIMARY KEY (id)
);
现在要根据node_id和server_id查询最新的记录时间:
查询每个服务的每个节点的最新记录。
select x
from xviid_server_status x,
(select server_id,node_id, max(creat_time) creat_time from xviid_server_status group by server_id, node_id) s
where x.server_id = s.server_id and x.node_id = s.node_id and x.creat_time = s.creat_time
查询指定服务在每个节点的最新记录:
select x
from xviid_server_status x,
(select server_id,node_id, max(creat_time) creat_time from xviid_server_status where server_id='aaaaa' group by server_id, node_id) s
where x.server_id = s.server_id and x.node_id = s.node_id and x.creat_time = s.creat_time
查询指定服务在指定节点的最新记录:
select x
from xviid_server_status x,
(select server_id,node_id, max(creat_time) creat_time from xviid_server_status where server_id='aaaaa' and node_id='bbbbb' group by server_id, node_id) s
where x.server_id = s.server_id and x.node_id = s.node_id and x.creat_time = s.creat_time
以上的语句在数据库可以查询出数据的情况下没有任何问题。但是当语句查询结果为空时,就会直接返回表的引用名称 x。
需要改select x 为 select x*。才能在查询结果为空时,返回空。
查询每个服务的每个节点的最新记录。
select x.*
from xviid_server_status x,
(select server_id,node_id, max(creat_time) creat_time from xviid_server_status group by server_id, node_id) s
where x.server_id = s.server_id and x.node_id = s.node_id and x.creat_time = s.creat_time
查询指定服务在每个节点的最新记录:
select x.*
from xviid_server_status x,
(select server_id,node_id, max(creat_time) creat_time from xviid_server_status where server_id='aaaaa' group by server_id, node_id) s
where x.server_id = s.server_id and x.node_id = s.node_id and x.creat_time = s.creat_time
查询指定服务在指定节点的最新记录:
select x.*
from xviid_server_status x,
(select server_id,node_id, max(creat_time) creat_time from xviid_server_status where server_id='aaaaa' and node_id='bbbbb' group by server_id, node_id) s
where x.server_id = s.server_id and x.node_id = s.node_id and x.creat_time = s.creat_time