检索随机id_post sql

我有下一张桌子

 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()