如何在MySQL中获取表的第一条记录和最后一条记录？
要获取第一条记录和最后一条记录，请使用UNION。LIMIT也用于获取所需的记录数。
让我们首先创建一个表-
mysql> create table DemoTable694 ( EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY, EmployeeName varchar(100), EmployeeSalary int );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable694(EmployeeName,EmployeeSalary) values('Chris',457647);
mysql> insert into DemoTable694(EmployeeName,EmployeeSalary) values('Robert',90883);
mysql> insert into DemoTable694(EmployeeName,EmployeeSalary) values('David',123532);
mysql> insert into DemoTable694(EmployeeName,EmployeeSalary) values('Mike',989322);使用select语句显示表中的所有记录-
mysql> select *from DemoTable694;
这将产生以下输出-
+------------+--------------+----------------+ | EmployeeId | EmployeeName | EmployeeSalary | +------------+--------------+----------------+ | 1 | Chris | 457647 | | 2 | Robert | 90883 | | 3 | David | 123532 | | 4 | Mike | 989322 | +------------+--------------+----------------+ 4 rows in set (0.00 sec)
以下是获取第一个和最后一个记录的查询-
mysql> (select *from DemoTable694 order by EmployeeId ASC LIMIT 1) UNION (select *from DemoTable694 order by EmployeeId DESC LIMIT 1);
这将产生以下输出-
+------------+--------------+----------------+ | EmployeeId | EmployeeName | EmployeeSalary | +------------+--------------+----------------+ | 1 | Chris | 457647 | | 4 | Mike | 989322 | +------------+--------------+----------------+ 2 rows in set (0.00 sec)