在 WHERE 中使用 ROW_NUMBER() 别名[重复]

在 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”明显作为别名字段存在时。

我做错了什么？

ps：我已经尝试使用 HAVING 子句

使用子查询：

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;

使用热膨胀系数：

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;

您收到该错误的原因是WHERE子句在之前处理SELECT条款。因此，引擎无法看到row_number尝试处理条件时作为列... row_number <= 3与您原来的查询。

此外，使用 CTE 具有与使用子查询相同的性能，但确实提高了可读性。