PostgreSQL缺少FROM子句条目

Question

我有以下疑问：

WITH sb AS (
    SELECT "shiftId", "sb.bookings"."state", "visibleAt" FROM bookings
    JOIN "memberConnections" ON "bookings"."memberId" = "memberConnections"."memberId"
    WHERE "shiftId"= 1714
)

SELECT
    COALESCE((SELECT COUNT(*) from sb where "bookings.state"='MATCHED'), 0) as matched FROM SB

它告诉我：ERROR: missing FROM-clause entry for table "sb.bookings" LINE 2: SELECT "shiftId", "sb.bookings"."state", "visibleAt" FRO...

我把它写成"sb.bookings"."state"，因为state在两个表中，但是它不允许我把它写成"bookings"."state" (我也不知道为什么)

有人知道这是怎么回事吗？

Answer 1

您的CTE为state列添加了一个不存在的标识符。而且，您还可以简化最后的查询：

WITH sb AS (
    SELECT shiftId, b.state, visibleAt
    FROM bookings b
    INNER JOIN memberConnections m
        ON b.memberId = m.memberId
    WHERE shiftId = 1714
)

SELECT
    COALESCE(COUNT(*), 0) AS matched
FROM sb
WHERE
    state = 'MATCHED';

您的CTE的引用版本：

WITH sb AS (
    SELECT "shiftId", b."state", "visibleAt"
    FROM "bookings" b
    INNER JOIN "memberConnections" m
        ON b."memberId" = m."memberId"
    WHERE "shiftId" = 1714
)