因此，我对基于状态获取最新数据的查询有一个问题
例如我有一张这样的桌子
Schema (PostgreSQL v12)
CREATE TABLE test(
id INTEGER,
user_id INTEGER,
product VARCHAR(20),
status VARCHAR(20)
);
INSERT INTO test VALUES(1,1,'WALK','ACTIVE');
INSERT INTO test VALUES(2,1,'RUN','ACTIVE');
INSERT INTO test VALUES(3,2,'WALK','INACTIVE');
INSERT INTO test VALUES(4,2,'RUN','ACTIVE');
INSERT INTO test VALUES(5,3,'WALK','UPDATING');
INSERT INTO test VALUES(6,3,'RUN','ACTIVE');
INSERT INTO test VALUES(7,4,'WALK','UPDATING');
INSERT INTO test VALUES(8,4,'RUN','INACTIVE');
INSERT INTO test VALUES(9,2,'RUN','UPDATING');
Query #1
SELECT * FROM test;
| id | user_id | product | status |
|---|---|---|---|
| 1 | 1 | WALK | ACTIVE |
| 2 | 1 | RUN | ACTIVE |
| 3 | 2 | WALK | INACTIVE |
| 4 | 2 | RUN | ACTIVE |
| 5 | 3 | WALK | UPDATING |
| 6 | 3 | RUN | ACTIVE |
| 7 | 4 | WALK | UPDATING |
| 8 | 4 | RUN | INACTIVE |
| 9 | 2 | RUN | UPDATING |
基本上，我需要一个查询来获取最新的用户id状态，以及仅用于活动和更新的状态.但是，如果存在活动和更新，他们会 Select 活动的.
| user_id | status |
|---|---|
| 1 | ACTIVE |
| 2 | ACTIVE |
| 3 | ACTIVE |
| 4 | UPDATING |
我try 了这个，但它只返回最新的值
SELECT distinct on (user_id) user_id, status
FROM test
ORDER BY user_id DESC