PostgreSQL:row_number:错误:列 "rn"不存在

我的查询需要帮助。我想添加一个 row_number 来分区我的字段，但出现错误:
错误:列“rn”不存在
第 22 行:和 rn 你在我的查询中发现一些奇怪的东西吗？非常感谢!

with location as
(select location, topcount
from pr.rankinglist_location
where topcount = 3
or (topcount = 10 and population_all > 150000)
or topcount = 25)

select store_displayname as restaurant_name,
    street,
    street_no,
    zipcode,
    city,
    topcount,
    ROW_NUMBER() OVER (PARTITION BY city
                              ORDER BY rposition DESC) rn,
    store_id as store_id
from pr.rankinglist_store s
join
location m on m.location = s.city
where
statkey = '2015'
and
topcount = 3
and rn <= 3
group by 1, 2, 3, 4, 5, 6, 7, 8
order by rposition

最佳答案

别名不可访问，WHERE 子句中不允许使用窗口函数。使用派生表:

...
select *
from (
    select
        store_displayname as restaurant_name,
        street,
        street_no,
        zipcode,
        city,
        topcount,
        ROW_NUMBER() OVER (PARTITION BY city
                          ORDER BY rposition DESC) rn,
        store_id as store_id
    from pr.rankinglist_store s
    join location m on m.location = s.city
    where statkey = '2015'
    and topcount = 3
    group by 1, 2, 3, 4, 5, 6, 7, 8
    order by rposition
    ) sub
where rn <= 3;

关于PostgreSQL:row_number:错误:列 "rn"不存在，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/34717141/