在MySQL中达到特定限制后获取有序记录
为此,您可以使用子查询。让我们首先创建一个表-
mysql> create table DemoTable618 ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,StudentFirstName varchar(100) );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable618(StudentFirstName) values('David'); mysql> insert into DemoTable618(StudentFirstName) values('Chris'); mysql> insert into DemoTable618(StudentFirstName) values('Robert'); mysql> insert into DemoTable618(StudentFirstName) values('Sam'); mysql> insert into DemoTable618(StudentFirstName) values('Mike'); mysql> insert into DemoTable618(StudentFirstName) values('Carol'); mysql> insert into DemoTable618(StudentFirstName) values('Bob'); mysql> insert into DemoTable618(StudentFirstName) values('John');
使用select语句显示表中的所有记录-
mysql> select *from DemoTable618;
这将产生以下输出-
+-----------+------------------+ | StudentId | StudentFirstName | +-----------+------------------+ | 1 | David | | 2 | Chris | | 3 | Robert | | 4 | Sam | | 5 | Mike | | 6 | Carol | | 7 | Bob | | 8 | John | +-----------+------------------+ 8 rows in set (0.00 sec)
这是在特定限制后获取有序记录的查询-
mysql> select *from (select StudentId,StudentFirstName from DemoTable618 where StudentId >= 5 order by StudentId limit 4) tbl order by tbl.StudentId desc;
这将产生以下输出-
+-----------+------------------+ | StudentId | StudentFirstName | +-----------+------------------+ | 8 | John | | 7 | Bob | | 6 | Carol | | 5 | Mike | +-----------+------------------+ 4 rows in set (0.00 sec)