MySQL 查询从最后 20 条记录中返回 5 条随机记录?
mysqlmysqli database
为此，您需要使用 ORDER BY 对记录进行排序。使用 RAND() 获取随机记录，并使用 LIMIT 5，因为我们只想显示 5 条随机记录。
首先我们创建一个表 −
mysql> create table DemoTable773 (StudentId int); Query OK, 0 rows affected (0.59 sec)
使用 insert 命令在表中插入一些记录 −
mysql> insert into DemoTable773 values(100); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable773 values(200); Query OK, 1 row affected (0.87 sec) mysql> insert into DemoTable773 values(300); Query OK, 1 row affected (1.59 sec) mysql> insert into DemoTable773 values(400); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable773 values(500); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable773 values(1); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable773 values(2); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable773 values(3); Query OK, 1 row affected (0.07 sec) mysql> insert into DemoTable773 values(4); Query OK, 1 row affected (0.73 sec) mysql> insert into DemoTable773 values(5); Query OK, 1 row affected (0.77 sec) mysql> insert into DemoTable773 values(6); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable773 values(7); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable773 values(8); Query OK, 1 row affected (0.50 sec) mysql> insert into DemoTable773 values(9); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable773 values(10); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable773 values(90); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable773 values(91); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable773 values(92); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable773 values(93); Query OK, 1 row affected (0.33 sec) mysql> insert into DemoTable773 values(94); Query OK, 1 row affected (0.38 sec) mysql> insert into DemoTable773 values(95); Query OK, 1 row affected (0.34 sec) mysql> insert into DemoTable773 values(96); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable773 values(97); Query OK, 1 row affected (0.27 sec)
使用 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)
