问题是要 Select 正确的ID,但您可以使用
CREATE tABLE agent_audit_log(id int, agent_id int , api text, at timestamp)
CREATE TABLE
INSERT INTO agent_audit_log VALUES (1,1,'A',now()), (2,1,'B',now()),(3,2,'A',now()), (4,2,'B',now())
INSERT 0 4
DELETE FROM agent_audit_log
WHERE id NOT IN (SELECT MIN(id) FROM agent_audit_log GROUP BY agent_id);
DELETE 2
SELECT * FROM agent_audit_log
id |
agent_id |
api |
at |
1 |
1 |
A |
2023-06-16 22:11:16.919099 |
3 |
2 |
A |
2023-06-16 22:11:16.919099 |
SELECT 2
INSERT INTO agent_audit_log VALUES (2,1,'A',now()), (4,2,'A',now()),(6,1,'A',now()), (6,2,'A',now())
INSERT 0 4
WITH CTE AS (
SELECT id, ROW_NUMBER() OVER(PARTITION BY agent_id ORDER BY at) rn
FROM agent_audit_log)
DELETE FROM agent_audit_log
WHERE id NOT IN (SELECT id FROM CTE WHERE rn = 1);
DELETE 4
SELECT *
FROM agent_audit_log
id |
agent_id |
api |
at |
1 |
1 |
A |
2023-06-16 22:11:16.919099 |
3 |
2 |
A |
2023-06-16 22:11:16.919099 |
SELECT 2
fiddle个