postgreSQL范围内的唯一数据

Question

我有一个这样的示例表：

代码	时间
A
A
A
B	10:30:00
A
B	10:46:01
B

假设您有一个名为codes的表，它有两列(code和time(，下面的查询将产生您要查找的确切结果(根据上面的表(：

WITH dup_codes AS (
SELECT code, time FROM codes c1
WHERE EXISTS (SELECT  1 from codes c2
WHERE c2.code = c1.code
-- this is where magic happens!
AND c2.time + interval '1 second' = c1.time
)
)
SELECT * FROM Codes
WHERE time NOT IN (SELECT time FROM dup_codes);

Db Fiddle：https://dbfiddle.uk/?rdbms=postgres_13&fiddle=c9c4def09b06f04f77d5fa71f29e6c57