mysql查最高薪水,查找员工表MySQL的最高和第二最高薪水

Suppose that you are given the following simple database table called Employee that has 2 columns named Employee ID and Salary:

Employee

Employee ID Salary

3 200

4 800

7 450

I wish to write a query select max(salary) as max_salary, 2nd_max_salary from employee

then it should return

max_salary 2nd_max_salary

800 450

i know how to find 2nd highest salary

SELECT MAX(Salary) FROM Employee

WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )

or to find the nth

SELECT FROM Employee Emp1 WHERE (N-1) = ( SELECT COUNT(DISTINCT(Emp2.Salary)) FROM Employee Emp2

WHERE Emp2.Salary > Emp1.Salary)

but i am unable to figureout how to join these 2 results for the desired result

解决方案

You can just run 2 queries as inner queries to return 2 columns:

select

(SELECT MAX(Salary) FROM Employee) maxsalary,

(SELECT MAX(Salary) FROM Employee

WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )) as [2nd_max_salary]