您可以使用子查询从Employee表中获取最高和第二最高薪水。
让我们首先创建一个表。创建表的查询如下-mysql>createtableEmployeeMaxAndSecondMaxSalary
->(
->EmployeeIdint,
->Employeenamevarchar(20),
->EmployeeSalaryint
->);
使用插入命令在表中插入一些记录-mysql>insertintoEmployeeMaxAndSecondMaxSalaryvalues(1,'John',34566);
mysql>insertintoEmployeeMaxAndSecondMaxSalaryvalues(2,'Bob',56789);
mysql>insertintoEmployeeMaxAndSecondMaxSalaryvalues(3,'Carol',44560);
mysql>insertintoEmployeeMaxAndSecondMaxSalaryvalues(4,'Sam',76456);
mysql>insertintoEmployeeMaxAndSecondMaxSalaryvalues(5,'Mike',65566);
mysql>insertintoEmployeeMaxAndSecondMaxSalaryvalues(6,'David',89990);
mysql>insertintoEmployeeMaxAndSecondMaxSalaryvalues(7,'James',68789);
mysql>insertintoEmployeeMaxAndSecondMaxSalaryvalues(8,'Robert',76543);
使用select语句显示表中的所有记录。查询如下-mysql>select*fromEmployeeMaxAndSecondMaxSalary;
以下是输出。+------------+--------------+----------------+
|EmployeeId|Employeename|EmployeeSalary|
+------------+--------------+----------------+
|1|John|34566|
|2|Bob|56789|
|3|Carol|44560|
|4|Sam|76456|
|5|Mike|65566|
|6|David|89990|
|7|James|68789|
|8|Robert|76543|
+------------+--------------+----------------+
8rowsinset(0.00sec)
使用子查询获取最高和第二最高薪水的查询-mysql>select(selectmax(EmployeeSalary)fromEmployeeMaxAndSecondMaxSalary)MaximumSalary,
->(selectmax(EmployeeSalary)fromEmployeeMaxAndSecondMaxSalary
->whereEmployeeSalarynotin(selectmax(EmployeeSalary)from
EmployeeMaxAndSecondMaxSalary))asSecondMaximumSalary;
以下是显示前两个薪水的输出-+---------------+---------------------+
|MaximumSalary|SecondMaximumSalary|
+---------------+---------------------+
|89990|76543|
+---------------+---------------------+
1rowinset(0.00sec)
