PostgreSQL - 使用with table_name as时出现语法错误

Question

我不确定需要做什么，我正在使用pgadmin3，但无论使用哪个GUI都会出现相同的错误。

以下是我的查询：

 with base_table as 
(select row_number() over (partition by p.customer_id order by p.payment_date ) as early_order,
        row_number() over (partition by p.customer_id order by p.payment_date desc) as last_order
    from payment p)

输入末尾的语法错误

我甚至尝试在末尾添加分号，但仍然不起作用：

with base_table as 
(select row_number() over (partition by p.customer_id order by p.payment_date ) as early_order,
        row_number() over (partition by p.customer_id order by p.payment_date desc) as last_order
    from payment p);

分号附近的语法错误

下面的内部查询运行良好：

select row_number() over (partition by p.customer_id order by p.payment_date ) as early_order,
        row_number() over (partition by p.customer_id order by p.payment_date desc) as last_order
    from payment p

只有在使用with table_name as时，错误才开始显示。

- user1411837

Answer 1

您需要添加主选择器：

with base_table as (
 select row_number() over(partition by p.customer_id order by p.payment_date) as early_order,
        row_number() over(partition by p.customer_id order by p.payment_date desc) as last_order
 from payment p)
SELECT *
FROM base_table

- Lukasz Szozda