难的MySQL自加入请说明 [英] Difficult MySQL self-join please explain

问题描述

这应该能够返回一个随机的帖子 ID.它也被认为是使用 MySQL 的最快方法.

This is supposed to be able to return a random post id. It is also said to be the fastest method using MySQL.

SELECT t.id 
FROM table t 
JOIN (SELECT(FLOOR(max(id) * rand())) AS maxid FROM table) 
AS tt 
ON t.id >= tt.maxid 
LIMIT 1     

似乎无法解决这个问题，请帮忙.

Can't seem to wrap my head around this, please help.

推荐答案

您从带​​有连接表"的表中随机选择一个 ID 号

You pick a random ID number from your table with the "joined table"

 SELECT(FLOOR(max(id) * rand())) AS maxid FROM table

如果 id 中没有空洞(连续整数)，这只会返回您可以使用的东西.这就是你加入这个的原因

This will only return something you can use if the id's don't have holes in them (consequtive integers). THat's why you're joining on this

 ON t.id >= tt.maxid 

您将获得所有高于随机数的 ID.然后限制获得第一个.

You'll get all the id's that are HIGHER then your random number. The limit then gets the first of those.

举个例子:假设你在表中有这些条目:1、2、5、7、8.

So an example: say you have these entries in table: 1, 2, 5, 7, 8.

地板随机数将小于 8，例如3.你加入会给你5、7和8但因为限制只返回5

The floored random number will be smaller then 8, e.g. 3. You'll join will give you 5, 7 and 8 but returns only 5 because of the limit

这篇关于难的MySQL自加入请说明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！