MySQL在最近50个条目中随机选择5条记录
为此，使用ORDERBYRAND()和子查询。让我们首先创建一个表
mysql> create table DemoTable1853
(
UserId int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(UserId)
);
Query OK, 0 rows affected (0.00 sec)使用Insert命令在表中插入一些记录
mysql> insert into DemoTable1853 values(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(); Query OK, 58 rows affected (0.00 sec) Records: 58 Duplicates: 0 Warnings: 0
使用select语句显示表中的所有记录
mysql> select * from DemoTable1853;
+--------+ | UserId | +--------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | | 11 | | 12 | | 13 | | 14 | | 15 | | 16 | | 17 | | 18 | | 19 | | 20 | | 21 | | 22 | | 23 | | 24 | | 25 | | 26 | | 27 | | 28 | | 29 | | 30 | | 31 | | 32 | | 33 | | 34 | | 35 | | 36 | | 37 | | 38 | | 39 | | 40 | | 41 | | 42 | | 43 | | 44 | | 45 | | 46 | | 47 | | 48 | | 49 | | 50 | | 51 | | 52 | | 53 | | 54 | | 55 | | 56 | | 57 | | 58 | +--------+ 58 rows in set (0.00 sec)
下面的查询只在最后50个条目中随机选择5行
mysql> select tbl.*
from (select tbl1.*
from DemoTable1853 tbl1
order by UserId DESC
LIMIT 50
) tbl
order by rand()
limit 5;+--------+ | UserId | +--------+ | 19 | | 24 | | 43 | | 36 | | 48 | +--------+ 5 rows in set (0.00 sec)