首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >在WHERE中使用ROW_NUMBER()别名

在WHERE中使用ROW_NUMBER()别名
EN

Stack Overflow用户
提问于 2018-02-24 05:46:58
回答 2查看 7.6K关注 0票数 6

在Postgresql 9.1+中,我尝试使用ROW_NUMBER()别名字段过滤WHERE子句中的结果集。这个查询运行得很好:

代码语言:javascript
复制
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“时:

代码语言:javascript
复制
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子句了

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2018-02-24 05:52:18

使用子查询:

代码语言:javascript
复制
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:

代码语言:javascript
复制
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与使用子查询具有相同的性能,但它确实提高了可读性。

票数 7
EN

Stack Overflow用户

发布于 2018-02-24 06:00:45

使用子查询:

代码语言:javascript
复制
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;
票数 3
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/48956679

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档