好吧,我今天一整天都在思考这个问题,无法让它发挥作用,基本上我试图从一个临时表中随机查询12张照片,该表包含活动登录用户尚未投票的所有照片。
错误显然是#1137-无法重新打开表:"r1"该临时表只能引用一次。
CREATE TEMPORARY TABLE unvoted_photos
SELECT *
FROM photo
WHERE photo.photo_uid
NOT IN
( SELECT photo_uid
FROM vote
WHERE vote.user_uid = '12345' ) ;
SELECT photo_uid, caption, storage_path
FROM unvoted_photos AS r1
JOIN (SELECT (RAND() *
(SELECT MAX(id)
FROM unvoted_photos)) AS sid)
AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 12;
由于性能问题,随机选择查询遵循了此示例。否则,这肯定会奏效。
SELECT ... FROM photo WHERE photo_uid NOT IN ( voted images subquery ) ORDER BY RAND() LIMIT 12
根据对该问题的讨论,这里有一种将两者合并在一起的方法,您应该可以立即运行。我会用JOIN
s重写它,但在这种情况下,我已经将临时表放入子查询中,这样您就可以看到发生了什么。
SELECT photo_uid, caption, storage_path
FROM (
SELECT photo_uid, caption, storage_path
FROM photo
WHERE photo.photo_uid
NOT IN
(
SELECT photo_uid
FROM vote
WHERE vote.user_uid = '12345'
)
) r1
JOIN (SELECT (RAND() *
(SELECT MAX(id)
FROM unvoted_photos)) AS sid)
AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 12;