检索随机id_post sql

由 zamet发布于 2020-05-18 09:38:00

我有下一张桌子

 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?

顽固不化

2020-05-18 09:38:00

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

一种方法是：

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;

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

情断心却相

2020-05-18 09:38:00

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

zamet

这家伙很懒，什么都没留下

积分
0
话题
0
评论
3143
注册排名
1572