postgresql 错误:位置为”“的”“处或其附近存在语法错误:857

hmae6n7t  于 2022-12-22  发布在  PostgreSQL
关注(0)|答案(1)|浏览(110)

对于查询:

with users_having_connected as (
    select u.id as user_id,
           (a.connected_at is not null) has_two_by_two
    from core_user u
    join core_profile p on u.id = p.user_id
    join core_conversation c on (c.profile1_id = p.id or c.profile2_id = p.id)
    join analytics_connection a on c.id = a.conversation_id
    group by u.id, (a.connected_at is not null)
)
select u.id as user_id,
       date_trunc('month', u.created at time zone 'UTC')::date as month,
       p.community_id,
       p.organization_id,
       p.profile_type_intention,
       (p.basic_account_completed and (p.is_mentor or p.is_entrepreneur)) as profile_is_completed,
       exists(select 1 from core_message where core_message.sender_id = p.id) as has_sent_a_message,
       exists(select 1 from users_having_connected where user_id = u.id) as has_two_by_two
with initiators as (
    exists(select 1 from core_admin_conversation_w_resp where initiator_id = p.id) as has_received_a_response_to_first_message
    from core_profile p
    where p.profile_type_intention is not null
    order by has_received_a_response_to_first_message desc
), responders as (
    select p.id as p_id,
    exists(select 1 from core_admin_conversation_w_resp where responder_id = p.id) as has_received_a_response_to_first_message
    from core_profile p
    where p.profile_type_intention is not null
    order by has_received_a_response_to_first_message desc
), boff as (
    select * from initiators
    union
    select * from responders
)
select distinct p_id from boff
where has_received_a_response_to_first_message = true
from core_user as u
join core_profile p on u.id = p.user_id
where
p.profile_type_intention is not null

我得到了以下结果:错误:位置为""的""处或其附近存在语法错误:857
我从两个独立工作的独立查询开始,并尝试将它们连接到一个查询中,以便用户可以使用两个查询的所有变量。
我不知道从哪里开始调试。

gojuced7

gojuced71#

请看这些行:

...
    group by u.id, (a.connected_at is not null)
)
select u.id as user_id,
...

右括号后面很可能缺少逗号,您需要为下面的SELECT指定一个名称

...
    group by u.id, (a.connected_at is not null)
), foo_bar as (
select u.id as user_id,
...

此外,在initiators as之前似乎还有一个额外的WITH

相关问题