mysql - 查找员工表 MySQL 的最大和第二个最大薪水

Question

假设您有一个名为 Employee 的简单数据库表，它有 2 列名为 Employee ID 和 Salary:

  Employee
  Employee ID    Salary
   3            200
   4            800
   7            450

我想写一个查询 select max(salary) as max_salary, 2nd_max_salary from employee

那么它应该返回

  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 Fiddle Demo