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 |
+--------+----------+--------+