mysql - 不使用限制和子查询无法从 emp 表中找到第二高的薪水

Question

SELECT
max(a.salary) as salary
FROM
tbl_emp as a,
tbl_emp as b
WHERE
a.salary < b.salary
and a.dept_Id = '57'
and a.Date_of_joining between '2015-01-01 00:00:00' and '2015-01-01 23:59:59';

Answer 1

这只是 rownum 的可视化。

create table tbl_emp
(   id int auto_increment primary key,
    salary int not null,
    key(salary) -- with few rows, not used, run thru EXPLAIN cmd to prove it
);
insert tbl_emp(salary) values (1),(2),(3),(4),(5);  -- affordable employees

set @rownum:=0;
select * from
(
    select @rownum:=@rownum+1 as rownum,id,salary
    from tbl_emp
    order by salary desc
    limit 2
) xxx
where rownum=2;

+--------+----+--------+
| rownum | id | salary |
+--------+----+--------+
|      2 |  4 |      4 |
+--------+----+--------+