employee Table;

+--------+------------------+
| emp_id | emp_name  salary |
+--------+----------+--------+
| 1      | James    |  2000  |
| 2      | Jack     |   4000 |
| 3      | Henry    |   6000 |
| 5      | John     |   8000 |
| 6      | Martin   |   6000 |`
| 7      | Deny     |   6000 |


我想获得第二高的薪水,根据表格第二高的薪水将是:

+--------+------------------+
| emp_id | emp_name  salary |
+--------+----------+--------+
| 3      | Henry    |   6000 |
| 6      | Martin   |   6000 |`
| 7      | Deny     |   6000 |


我怎样才能做到这一点?

我不能用:-

select * from employee order by salary desc Limit 1,1


因为它总是只显示一个记录。

任何帮助将不胜感激。

谢谢!!!

最佳答案

可以使用此查询:

select * from employee where salary =
(select max(salary) from employee where salary <
(select max(salary) from employee));


对于数据集,结果如下:

+--------+----------+--------+
| emp_id | emp_name | salary |
+--------+----------+--------+
|      3 | Henry    |   6000 |
|      6 | Martin   |   6000 |
|      7 | Deny     |   6000 |
+--------+----------+--------+

10-07 14:03