查找员工表MySQL的最高和第二最高薪水 [英] Find max and second max salary for a employee table MySQL

问题描述

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

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

我想写一个查询，选择max(salary)作为max_salary，雇员的2nd_max_salary

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

然后它应该返回

  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 )

或找到第n个

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

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

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

推荐答案

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

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]

SQL小提琴演示

这篇关于查找员工表MySQL的最高和第二最高薪水的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！