sql - Postgres 查询在 "Where"处返回 SQL 错误语法错误

Question

我有以下查询，当我执行它时出现错误

INSERT INTO AM_EVENT_STATUS (EVENT_STATUS_ID, EVENT_ID, TIMESTAMP,OLD_STATUS_ID, NEW_STATUS_ID, NOTE, USERNAME)
SELECT
"external_id" AS EVENT_STATUS_ID,
"external_id" AS EVENT_ID,
"timestamp"   AS TIMESTAMP,
NULL          AS OLD_STATUS_ID,
'unknown'     AS NEW_STATUS_ID,
NULL          AS NOTE,
NULL          AS USERNAME
FROM (SELECT "2cba77463c899fee1656b367e39892"
  FROM "2cba77463c899fee1656b367e39892"
  INNER JOIN (SELECT "external_id", MAX("timestamp") AS "timestamp"
            FROM "2cba77463c899fee1656b367e39892"
              GROUP BY "external_id") LATEST WHERE "2cba77463c899fee1656b367e39892"."external_id" = LATEST."external_id"
    AND "2cba77463c899fee1656b367e39892"."timestamp" = LATEST."timestamp"
    AND "2cba77463c899fee1656b367e39892"."event_type" = 'transaction_update');

错误是:

SQL Error [42601]: ERROR: syntax error at or near "WHERE" Position: 601 org.postgresql.util.PSQLException: ERROR: syntax error at or near "WHERE" Position: 601

我在这里做错了什么？提前致谢。

Answer 1

将 LATEST WHERE 更改为 LATEST ON。您有一个 JOIN，因此正确的比较子句是 ON，而不是 WHERE。

最后一个括号后还需要一个表别名。