错误代码:
select
distinct Salary
from
(
select
Salary,@r := IF(@p = Salary,@r,@r+1) ,@p:=Salary
from
Employee, (SELECT @r:=0, @p:=null)init
-- init作为别名
order by
Salary desc
) as tmp
where @r = N
正确代码:
SELECT
DISTINCT salary
FROM
(SELECT
salary, @r:=IF(@p=salary, @r, @r+1) AS rnk, @p:= salary
FROM
employee, (SELECT @r:=0, @p:=NULL)init
ORDER BY
salary DESC) tmp
WHERE rnk = N
为什么非要给@r 取别名啊???
因为执行顺序时先执行from 再执行where 当执行where时 @r的值此时为最后一个成绩的排名