检索随机id_post sql

由 发布于 MySQLrandomsql-order-bysql-limit
 收藏

我有下一张桌子

 id_post | post


 24         A
 26         B
 39         C
 57         D

我想检索一个随机的id_post

 SELECT id_post
 FROM  posts
 WHERE rand()

I would like to retrieve a single value 24, 26, 39 or 57 how can I make that work?

评论
顽固不化
顽固不化

如果只有四行，那么操作就没关系。但是，如果您有一个大表，则希望限制要排序的数据量。

一种方法是：

select p.id_post
from posts p cross join
     (select count(*) as cnt from posts) pp
where rand() < 100.0 / pp.cnt
order by rand()
limit 1;

尽管这需要扫描表，但不需要对整个表进行排序。

点赞
评论
情断心却相
情断心却相

You seem to want ORDER BY RAND() and LIMIT 1:

SELECT id_post
FROM  posts
ORDER BY rand()
LIMIT 1

You did not tell which database you are using. The actual syntax for RAND() and LIMIT 1 does vary accross databases. The above would work in MySQL. There are equivalent syntaxes in other databases, for example:

Postgres：

SELECT id_post
FROM  posts
ORDER BY random()
LIMIT 1

SQL Server：

SELECT TOP (1) id_post
FROM  posts
ORDER BY newid()
点赞
评论