Topic describes

Select emp_no, salary, last_name, first_name from last_name (to_date=’9999-01-01′)

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
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`));
Copy the code

The employee with the highest salary is the employee with the second highest salary. The employee with the highest salary is the employee with the second highest salary.

Select Max (salary) from salaries WHERE to_date=’9999-01-01′ select * from salaries where salaries! =(select Max (salary) from salaries WHERE to_date=’9999-01-01′) 1 remaining Max salary: select max(salary) from ( select * from salaries where salary! =(select Max (salary) from salaries WHERE to_date=’9999-01-01′)) WHERE to_date=’9999-01-01′

select s.emp_no,s.salary,e.last_name,e.first_name from salaries s,employees e where s.emp_no=e.emp_no and s.to_date='9999-01-01' and s.salary=( select max(salary) from ( select * from salaries where salary! =( select max(salary) from salaries where to_date='9999-01-01' ) ) where to_date='9999-01-01' );Copy the code

Solution 2: Nesting of MAX () functions is also possible

select e.emp_no, Max(s.salary) as salary, e.last_name, e.first_name from employees e,salaries s where e.emp_no=s.emp_no and s.to_date='9999-01-01' and s.salary! = (select Max(salary) from salaries where to_date='9999-01-01')Copy the code

The first few records of each group can be taken by mysql group. The advantage is that you can take any salary of the staff. The disadvantage is that it is not easy to understand

select s.emp_no, s.salary, e.last_name, e.first_name from salaries s join employees e on s.emp_no=e.emp_no
and s.to_date='9999-01-01'
and s.emp_no=(select s1.emp_no from salaries s1 join salaries s2 on s1.salary<=s2.salary 
  and s1.to_date='9999-01-01' and s2.to_date='9999-01-01'
  group by s1.emp_no having count(1) = 2);
Copy the code

Reference sources: Niuke network