博客文章地址:http://blog.csdn.net/macwhirr123/article/details/7489484
Select*from(Select(@rowNo :=@rowNo+1)ASrowno, pwdfrompur_admininfo, (Select@rowNo := 0) b) tmpwhererowno=(SelectRound(Rand() * 10000000 % (SelectCount(*)frompur_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,然后再取模,但不知道为什么更大的数字就不行了。
另一种实现方式:
SELECT*FROMpur_admininfoASt1JOIN(SELECTROUND(RAND() * ((SELECTMAX(rid)FROMpur_admininfo) - (SELECTMIN(rid)FROMpur_admininfo)) + (SELECTMIN(rid)FROMpur_admininfo))ASrid)ASt2WHEREt1.rid >= t2.ridORDERBYt1.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;