PostgreSQL查询模式
问题描述：
我想要一个列出所有状态为“活动”的客户的查询。该查询将返回标记为活动的客户列表。我的问题是我迷失在查询引用其他表的表上。这是我的模式。PostgreSQL查询模式
CREATE TABLE Customer (
ID BIGSERIAL PRIMARY KEY NOT NULL,
fNAME TEXT NOT NULL,
lNAME TEXT NOT NULL,
create_date DATE NOT NULL DEFAULT NOW()
);
CREATE TABLE CustomerStatus (
recordID BIGSERIAL NOT NULL,
ID BIGSERIAL REFERENCES Customer NOT NULL,
status TEXT NOT NULL,
create_date DATE NOT NULL DEFAULT NOW()
);
INSERT INTO Customer (fNAME, lNAME) VALUES ('MARK', 'JOHNSON'), ('ERICK', 'DAWN'), ('MAY', 'ERICKSON'), ('JESS', 'MARTIN');
INSERT INTO CustomerStatus (ID, status) VALUES (1, 'pending'), (1, 'active');
INSERT INTO CustomerStatus (ID, status) VALUES (2, 'pending'), (2, 'active'), (2, 'cancelled');
INSERT INTO CustomerStatus (ID, status) VALUES (3, 'pending'), (3, 'active');
INSERT INTO CustomerStatus (ID, status) VALUES (4, 'pending');
答
我鼓起勇气假设RECORD_ID串行=>最新的ID将是最后的，产生这种QRY：
t=# with a as (
select *, max(recordid) over (partition by cs.id)
from Customer c
join CustomerStatus cs on cs.id = c.id
)
select *
from a
where recordid=max and status = 'active';
id | fname | lname | create_date | recordid | id | status | create_date | max
----+-------+----------+-------------+----------+----+--------+-------------+-----
1 | MARK | JOHNSON | 2017-04-27 | 2 | 1 | active | 2017-04-27 | 2
3 | MAY | ERICKSON | 2017-04-27 | 7 | 3 | active | 2017-04-27 | 7
(2 rows)
Time: 0.450 ms
有模式中的一些奇怪的事情。 1. Customer.ID NOT NULL约束是多余的。 2.为什么CustomerStatus.ID输入BIGSERIAL？我想，它应该是BIGINT。 3.主要问题（你应该解释它） - 1客户可以有多个CustomerStatus？如果是（并且CustomerStatus表类似CustomerStatusHistory），则将约束UNIQUE（ID，create_date）添加到CustomerStatus。否则，约束是UNIQUE（ID），客户只能拥有一个状态。 – Eugene
固定感谢调用冗余 – MarkM