说明

Where 执行的优先级 优先于 over() 窗口函数，也就是说：先执行 where 子句，然后执行 over()窗口函数

所以下面sql会报错：

select deptno,ename,sal,
ROW_NUMBER () over ( PARTITION by deptno order by sal desc ) as row_num
from emp
where row_num <= 2

错误如下：

org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [10004] [42000]: Error while compiling statement: FAILED: SemanticException [Error 10004]: Line 7:6 Invalid table alias or column reference 'row_num': (possible column names are: empno, ename, job, mgr, hiredate, sal, comm, deptno)

解决

使用 with 语句：

with tbl as (
    select deptno,ename,sal,
    ROW_NUMBER () over ( PARTITION by deptno order by sal desc ) as row_num
    from emp
)
select deptno,ename,sal,row_num from tbl where row_num <= 2

原文出处：/show_1IX4OSxEZKz2.html