MySql从表结果集中随机取一条数据

最新推荐文章于 2021-03-03 14:41:19 发布

最爱陌生人

最新推荐文章于 2021-03-03 14:41:19 发布

阅读量1.3k

点赞数

分类专栏：数据库

数据库专栏收录该内容

8 篇文章

订阅专栏

该功能的实现基于“mysql 查找结果中自动加序号列 ”

博客文章地址：http://blog.youkuaiyun.com/macwhirr123/article/details/7489484

[sql] view plain copy print ?

Select * from (Select (@rowNo :=@rowNo+1) AS rowno, pwd from pur_admininfo, (Select @rowNo := 0) b) tmp where rowno=(Select Round(Rand() * 10000000 % (Select Count(*) from pur_admininfo))) limit 1;

Select * from (Select (@rowNo :=@rowNo+1) AS rowno, pwd from pur_admininfo, (Select @rowNo := 0) b) tmp where rowno=(Select Round(Rand() * 10000000 % (Select Count(*) from pur_admininfo))) limit 1;

为了可以随机得到表结果集中的任何一条记录，这里乘以10000000，然后再取模，但不知道为什么更大的数字就不行了。

另一种实现方式：

[sql] view plain copy print ?

SELECT * FROM pur_admininfo AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(rid) FROM pur_admininfo) - (SELECT MIN(rid) FROM pur_admininfo)) + (SELECT MIN(rid) FROM pur_admininfo)) AS rid) AS t2 WHERE t1.rid >= t2.rid ORDER BY t1.rid LIMIT 1;

SELECT * FROM pur_admininfo AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(rid) FROM pur_admininfo) - (SELECT MIN(rid) FROM pur_admininfo)) + (SELECT MIN(rid) FROM pur_admininfo)) AS rid) AS t2 WHERE t1.rid >= t2.rid ORDER BY t1.rid LIMIT 1;