题目描述:
CREATE TABLE salaries
(
emp_no
int(11) NOT NULL,
salary
int(11) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (emp_no
,from_date
));
输出描述:
代码:
SELECT emp_no,salary
FROM salaries
WHERE to_date = '9999-01-01'
AND salary = (
SELECT DISTINCT salary
FROM salaries
WHERE to_date = '9999-01-01'
ORDER BY salary desc limit 1,1);
讲解:
这里主要是考distinct和limit,这里使用distinct主要是去重,因为工资第一多的可能有多人,这样就排除后就选择了第二个;先倒序排序,接着limit 1,1是从第二个开始选取,然后选取一个