postgresql - 显示服务器名称、实例 ID 和活动会话数的视图（如果结束时间戳为空，则会话处于活动状态）

Question

CREATE TABLE instances(
    ser_name VARCHAR(20) NOT NULL,
    id INTEGER NOT NULL ,
    ser_ip VARCHAR(16) NOT NULL,
    status VARCHAR(10) NOT NULL,
    creation_ts TIMESTAMP,
    CONSTRAINT instance_id PRIMARY KEY(id)
);

CREATE TABLE characters(
    nickname VARCHAR(15) NOT NULL,
    type VARCHAR(10) NOT NULL,
    c_level INTEGER NOT NULL,
    game_data VARCHAR(40) NOT NULL,
    start_ts TIMESTAMP ,
    end_ts TIMESTAMP NULL ,
    player_ip VARCHAR(16) NOT NULL,
    instance_id INTEGER NOT NULL,
    player_username VARCHAR(15),
    CONSTRAINT chara_nick PRIMARY KEY(nickname)
); 
ALTER TABLE
     instances ADD CONSTRAINT ins_ser_name FOREIGN KEY(ser_name) REFERENCES servers(name);
ALTER TABLE
    instances ADD CONSTRAINT ins_ser_ip FOREIGN KEY(ser_ip) REFERENCES servers(ip);
ALTER TABLE
    characters ADD CONSTRAINT chara_inst_id FOREIGN KEY(instance_id) REFERENCES instances(id);
ALTER TABLE
    characters ADD CONSTRAINT chara_player_username FOREIGN KEY(player_username) REFERENCES players(username);

insert into instances values
('serverA','1','138.201.233.18','active','2020-10-20'),
('serverB','2','138.201.233.19','active','2020-10-20'),
('serverE','3','138.201.233.14','active','2020-10-20');

insert into characters values
('characterA','typeA','1','Game data of characterA','2020-07-18 02:12:12','2020-07-18 02:32:30','192.188.11.1','1','nabin123'),
('characterB','typeB','3','Game data of characterB','2020-07-19 02:10:12',null,'192.180.12.1','2','rabin123'),
('characterC','typeC','1','Game data of characterC','2020-07-18 02:12:12',null,'192.189.10.1','3','sabin123'),
('characterD','typeA','1','Game data of characterD','2020-07-18 02:12:12','2020-07-18 02:32:30','192.178.11.1','2','nabin123'),
('characterE','typeB','3','Game data of characterE','2020-07-19 02:10:12',null,'192.190.12.1','1','rabin123'),
('characterF','typeC','1','Game data of characterF','2020-07-18 02:12:12',null,'192.188.10.1','3','sabin123'),
('characterG','typeD','1','Game data of characterG','2020-07-18 02:12:12',null,'192.188.13.1','1','nabin123'),
('characterH','typeD','3','Game data of characterH','2020-07-19 02:10:12',null,'192.180.17.1','2','bipin123'),
('characterI','typeD','1','Game data of characterI','2020-07-18 02:12:12','2020-07-18 02:32:30','192.189.18.1','3','dhiraj123'),
('characterJ','typeD','3','Game data of characterJ','2020-07-18 02:12:12',null,'192.178.19.1','2','prabin123'),
('characterK','typeB','4','Game data of characterK','2020-07-19 02:10:12','2020-07-19 02:11:30','192.190.20.1','1','rabin123'),
('characterL','typeC','2','Game data of characterL','2020-07-18 02:12:12',null,'192.192.11.1','3','sabin123'),
('characterM','typeC','3','Game data of characterM','2020-07-18 02:12:12',null,'192.192.11.1','2','sabin123');

在这里，我需要一个显示服务器名称、实例 ID 和活动会话数的视图（如果结束时间戳为空，则会话处于活动状态）。我的代码错了还是别的什么？我开始学习，所以希望得到积极的最佳答案。

我的观点

create view active_sessions as
select i.ser_name, i.id, count(end_ts) as active
from instances i, characters c
where i.id=c.instance_id and c.end_ts = null
group by i.ser_name, i.id;

标签： postgresqljoincountwhere-clausesql-view

Answer 1

这不符合您的要求：

where i.id = c.instance_id and c.end_ts = null

没有什么是等于的null。您需要is null对照null.

此外，count(end_ts)将始终产生0，正如我们已经知道end_ts的那样null，它count()不考虑。

最后，我强烈建议使用标准连接（使用on关键字），而不是隐式连接（在from子句中使用逗号）：这种几十年前的旧语法不应该在新代码中使用。我认为 aleft join更接近你想要的（它也会考虑到根本没有字符的实例）。

所以：

create view active_sessions as
select i.ser_name, i.id, count(c.nickname) as active
from instances i
left join characters c on i.id = c.instance_id and c.end_ts is null
group by i.ser_name, i.id;