MySQL查询返回最近20条记录中的5条随机记录？
为此，您需要使用ORDERBY来订购记录。与使用RAND()获得随机记录和LIMIT5，因为我们希望只显示5个随机记录。
让我们首先创建一个表-
mysql> create table DemoTable773 (StudentId int);
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable773 values(100); mysql> insert into DemoTable773 values(200); mysql> insert into DemoTable773 values(300); mysql> insert into DemoTable773 values(400); mysql> insert into DemoTable773 values(500); mysql> insert into DemoTable773 values(1); mysql> insert into DemoTable773 values(2); mysql> insert into DemoTable773 values(3); mysql> insert into DemoTable773 values(4); mysql> insert into DemoTable773 values(5); mysql> insert into DemoTable773 values(6); mysql> insert into DemoTable773 values(7); mysql> insert into DemoTable773 values(8); mysql> insert into DemoTable773 values(9); mysql> insert into DemoTable773 values(10); mysql> insert into DemoTable773 values(90); mysql> insert into DemoTable773 values(91); mysql> insert into DemoTable773 values(92); mysql> insert into DemoTable773 values(93); mysql> insert into DemoTable773 values(94); mysql> insert into DemoTable773 values(95); mysql> insert into DemoTable773 values(96); mysql> insert into DemoTable773 values(97);
使用select语句显示表中的所有记录-
mysql> select *from DemoTable773;
这将产生以下输出-
+-----------+ | StudentId | +-----------+ | 100 | | 200 | | 300 | | 400 | | 500 | | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | | 90 | | 91 | | 92 | | 93 | | 94 | | 95 | | 96 | | 97 | +-----------+ 23 rows in set (0.00 sec)
以下是从中返回5条随机记录的查询
最后20条记录-
mysql> select *from ( select * from DemoTable773 order by StudentId desc limit 20 ) AS RANDOM_OUTPUT order by rand()limit 5;
这将产生以下输出-
+-----------+ | StudentId | +-----------+ | 95 | | 4 | | 10 | | 7 | | 300 | +-----------+ 5 rows in set (0.51 sec)