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';
最佳答案
这只是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 |
+--------+----+--------+
关于mysql - 不使用限制和子查询就无法从emp表中找到第二高的薪水,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/32069489/