SQL查询问题排查：获取emp表第二高工资的错误分析及替代方案

你的SQL报错核心原因是窗口函数的别名无法在WHERE子句中直接使用。SQL的执行顺序是：FROM → WHERE → GROUP BY → HAVING → SELECT（包括窗口函数计算） → ORDER BY。当执行WHERE子句时，SELECT里定义的r还未被计算生成，数据库自然识别不了这个列名，所以会报错。

另外还有个逻辑问题：原语句用order by sal是升序排序，得到的r=2是第二低工资，要获取第二高工资必须改成order by sal desc。

可以通过子查询或CTE先算出排名，再在外层过滤排名为2的记录：

方法1：子查询 #

select ename, r
from (
    select 
        ename,
        dense_rank() over (order by sal desc) as r
    from emp
) t
where r = 2

方法2：CTE（公共表表达式） #

with emp_ranked as (
    select 
        ename,
        dense_rank() over (order by sal desc) as r
    from emp
)
select ename, r
from emp_ranked
where r = 2

方法1：LIMIT + OFFSET（适合无并列工资场景） #

如果工资没有重复值，能直接取第二高工资：

select ename, sal
from emp
order by sal desc
limit 1 offset 1

缺点：如果有多个员工工资并列第二高，这个方法只会返回其中一个。

方法2：子查询过滤法（获取所有并列第二高的员工） #

通过嵌套子查询找出第二高工资的数值，再匹配对应员工：

select ename, sal
from emp
where sal = (
    select max(sal)
    from emp
    where sal < (select max(sal) from emp)
)

缺点：只适合获取第二高，扩展到第N高时需要多层嵌套，比较繁琐。

方法3：ROW_NUMBER()（区分并列工资的不同员工） #

如果需要给工资相同的员工分配唯一排名（比如按姓名排序区分），可以用ROW_NUMBER()替代DENSE_RANK()：

select ename, r
from (
    select 
        ename,
        row_number() over (order by sal desc, ename) as r
    from emp
) t
where r = 2

和DENSE_RANK()的区别：DENSE_RANK()给相同工资的员工分配相同排名，后续排名不跳跃；ROW_NUMBER()则给每个员工分配唯一排名，哪怕工资一样。

内容的提问来源于stack exchange，提问作者Vishal