我有下一张桌子
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?
我有下一张桌子
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()