postgresql:如何在where子句中使用变量(行的某些列值)

SELECT t.*
FROM tablename t
WHERE t.time > (SELECT t2.time - INTERVAL '5 minute'
                FROM tablename t2
                ORDER BY id desc
                LIMIT 1
               );

如果这确实不是最近的时间,并且您希望在五分钟内得到任何结果,那么将计算移到 FROM

SELECT t.*
FROM tablename t CROSS JOIN
     (SELECT t2.time - INTERVAL '5 minute'
      FROM tablename t2
      ORDER BY id desc
      LIMIT 1
     ) last_row
WHERE t.time > last_row.time - INTERVAL '5 minute' AND
      t.time <= last_row.time;

如果您需要最新时间以及5分钟内的任何内容,也可以使用窗口功能:

SELECT t.*
FROM (SELECT t.*,
             MAX(t.time) OVER () as max_time
      FROM t
     ) t
WHERE t.time > max_time - INTERVAL '5 minute';