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

Question

假设您得到以下名为Employee的简单数据库表，该表具有2个列，分别名为Employee ID和Salary：

  Employee
  Employee ID    Salary
   3            200
   4            800
   7            450

我想写一个查询，从员工那里选择max（salary）作为max_salary，2nd_max_salary

然后它应该返回

  max_salary   2nd_max_salary
   800             450

我知道如何找到第二高的薪水

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

或找到第n个

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

但我无法弄清楚如何将这2个结果结合起来以获得所需的结果

Answer 1

您可以只运行2个查询作为内部查询以返回2列：

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]

SQL小提琴演示