postgresql 为什么我不能只使用一个SELECT子句和RANK()以及WHERE条件[duplicate]

dz6r00yl  于 2022-12-03  发布在  PostgreSQL
关注(0)|答案(2)|浏览(147)

This question already has answers here:

Using an Alias column in the where clause in Postgresql (6 answers)
How to use row_number in a where clause (1 answer)
Closed yesterday.
I can get the rank alias with this query:

SELECT *, 
  RANK() OVER (PARTITION BY some_field ORDER BY value) AS rk
FROM my_table

Result:
| some_field | value | rk |
| ------------ | ------------ | ------------ |
| same | 10 | 1 |
| same | 20 | 2 |
| same | 30 | 3 |
And I tried to query with:

SELECT *, 
  RANK() OVER (PARTITION BY some_field ORDER BY value) AS rk
FROM my_table
WHERE rk = 1

I got this error message column "rk" does not exist
If I tried a subquery, it works :

SELECT *
FROM (
  SELECT *,
    RANK() OVER (PARTITION BY some_field ORDER BY value) AS rk
  FROM my_table
) AS t
WHERE rk = 1

Result:
| some_field | value | rk |
| ------------ | ------------ | ------------ |
| same | 10 | 1 |
But my question is why can't we use just one SELECT to do so.
Is it because I use a function in my query?

elcex8rz

elcex8rz1#

是的。。你不能在where子句中使用列名。首先了解SQL执行流程。根据你的SQL语句,它将按以下顺序执行。

  1. From子句。
    1.应用筛选where子句
    1.执行select语句。
    所以在你的sql语句中,rk列是在第三步生成的。但是你在第二步访问,这是不可能的。希望它能被清除
w1jd8yoj

w1jd8yoj2#

您可以使用秩表达式作为约束条件...如下所示。

SELECT *, 
 1 AS rk
FROM my_table
WHERE  RANK() OVER (PARTITION BY some_field ORDER BY value) = 1

希望能有所帮助。

相关问题