MySQL查询选择最高和最低薪水行？
为此，将子查询与MIN()和一起使用MAX()。要同时显示最大值和最小值，请使用UNIONALL。让我们首先创建一个表-
mysql> create table DemoTable -> ( -> EmployeeName varchar(20), -> EmployeeSalary int -> );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable values('Bob',8800);
mysql> insert into DemoTable values('Chris',9800);
mysql> insert into DemoTable values('David',7600);
mysql> insert into DemoTable values('Sam',9600);使用select语句显示表中的所有记录-
mysql> select *from DemoTable;
这将产生以下输出-
+--------------+----------------+ | EmployeeName | EmployeeSalary | +--------------+----------------+ | Bob | 8800 | | Chris | 9800 | | David | 7600 | | Sam | 9600 | +--------------+----------------+ 4 rows in set (0.00 sec)
这是选择最低工资行的查询-
mysql> select *from DemoTable -> where EmployeeSalary in ( select max(EmployeeSalary) from DemoTable -> union all -> select min(EmployeeSalary) from DemoTable -> );
这将产生以下输出-
+--------------+----------------+ | EmployeeName | EmployeeSalary | +--------------+----------------+ | Chris | 9800 | | David | 7600 | +--------------+----------------+ 2 rows in set (0.05 sec)