在WHERE中使用ROW_NUMBER()别名
在WHERE中使用ROW_NUMBER()别名
提问于 2018-02-24 05:46:58
在Postgresql 9.1+中，我尝试使用ROW_NUMBER()别名字段过滤WHERE子句中的结果集。这个查询运行得很好：
SELECT inv.client_pk,
inv.invoice_pk, inv.contract_pk,
ROW_NUMBER() OVER ( PARTITION BY inv.client_pk ORDER BY inv.client_pk) as row_number
FROM controllr.client as cli
LEFT JOIN controllr.invoice as inv ON inv.client_pk = cli.client_pk
WHERE client_status != 3;但是当我在WHERE中添加"row_number“时：
SELECT inv.client_pk,
inv.invoice_pk, inv.contract_pk,
ROW_NUMBER() OVER ( PARTITION BY inv.client_pk ORDER BY inv.client_pk) as row_number
FROM controllr.client as cli
LEFT JOIN controllr.invoice as inv ON inv.client_pk = cli.client_pk
WHERE client_status != 3
AND row_number <= 3;它给了我一个错误：
列"row_number“不存在
当字段"row_number“明确作为带别名的字段存在时。
我做错了什么？
附言:我已经试过HAVING子句了
回答 2
Stack Overflow用户
回答已采纳
发布于 2018-02-24 05:52:18
使用子查询：
SELECT
*
FROM
(SELECT
inv.client_pk, inv.invoice_pk, inv.contract_pk,
ROW_NUMBER() OVER (PARTITION BY inv.client_pk ORDER BY inv.client_pk) AS row_number
FROM
controllr.client as cli
LEFT JOIN
controllr.invoice as inv ON inv.client_pk = cli.client_pk
WHERE
client_status != 3) AS sub
WHERE
row_number <= 3;使用CTE：
WITH cte AS
(
SELECT
inv.client_pk, inv.invoice_pk, inv.contract_pk,
ROW_NUMBER() OVER ( PARTITION BY inv.client_pk ORDER BY inv.client_pk) AS row_number
FROM
controllr.client as cli
LEFT JOIN
controllr.invoice as inv ON inv.client_pk = cli.client_pk
WHERE
client_status != 3
)
SELECT *
FROM cte
WHERE row_number <= 3;收到该错误的原因是因为在处理SELECT子句之前处理了WHERE子句。因此，在尝试使用原始查询处理条件... row_number <= 3时，引擎无法将row_number视为列。
此外，使用CTE与使用子查询具有相同的性能，但它确实提高了可读性。
Stack Overflow用户
发布于 2018-02-24 06:00:45
使用子查询：
SELECT client_pk, invoice_pk, contract_pk
FROM
(
SELECT inv.client_pk, inv.invoice_pk, inv.contract_pk,
ROW_NUMBER() OVER
( PARTITION BY inv.client_pk
ORDER BY inv.client_pk) as row_number
FROM controllr.client as cli
LEFT JOIN controllr.invoice as inv ON inv.client_pk = cli.client_pk
WHERE client_status !=3
) t
WHERE row_number <= 3;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/48956679
复制相关文章
相似问题
腾讯云开发者
