mysql - 如何在mysql中获取具有相同工资值的第二个员工姓名的最低和最高工资的员工姓名？

Question

我有一个表 odd_empcop ，其中最低工资 = 2000 和最高工资 =8000 ，有很多员工的最高工资为 8000。我想获得第二个员工的最低工资和最高工资的员工姓名。

我已应用此代码：

(select emp_name, salary from odd_empcop
where salary = (select min(salary) from odd_empcop
                    order by salary ))
union
(select  emp_name, salary from odd_empcop
where salary = (select max(salary) from odd_empcop
    
                order by salary desc ));

得到如下输出：

emp_name salary
Gautham  2000
Melinda  8000
Cory     8000
Vikram   8000

但我想得到输出：

emp_name salary
Gautham  2000
Cory     8000

我懂了！！！！

(select emp_name, salary from odd_empcop
where salary = (select min(salary) from odd_empcop
                    order by salary ))
union
(select  emp_name, salary from odd_empcop
where salary = (select max(salary) from odd_empcop

                order by salary desc ) limit 1,1);

标签： mysqlsql

Answer 1

您可以使用ROW_NUMBER分析函数获取薪水最低的员工和第二名员工的详细信息，按薪水降序排列，然后按姓名排序：

SELECT emp_name,
       salary
FROM   (
  SELECT emp_name,
         salary,
         ROW_NUMBER() OVER (ORDER BY salary ASC,  emp_name ASC) AS rn_min,
         ROW_NUMBER() OVER (ORDER BY salary DESC, emp_name ASC) AS rn_max
  FROM   odd_empcop
) e
WHERE  rn_min = 1
OR     rn_max = 2;

其中，对于样本数据：

CREATE TABLE odd_empcop (
  salary NUMERIC(10,2),
  emp_name VARCHAR(20)
);

INSERT INTO odd_empcop (emp_name, salary)
SELECT 'Gautham', 2000 FROM DUAL UNION ALL
SELECT 'Melinda', 8000 FROM DUAL UNION ALL
SELECT 'Cory',    8000 FROM DUAL UNION ALL
SELECT 'Vikram',  8000 FROM DUAL;

输出：

emp_name	薪水
梅琳达	8000.00
高瑟姆	2000.00

MySQL db<>fiddle Oracle db<>fiddle