检索随机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()