mysql - 如何在没有子选择的情况下限制从 LEFT JOIN 返回的结果数？

Question

我有以下查询，但它从 profile_photos 表返回多个条目。是否可以从个人资料照片表中返回一个随机条目？如果不是随机的，是否可以将结果数限制为 1？

这是我当前的查询:

$sql = "SELECT * FROM login_users " .
       "LEFT JOIN profile_photos ON profile_photos.user_id = login_users.user_id " .
       "LEFT JOIN profiles ON profiles.user_id = login_users.user_id " .
       "WHERE login_users.restricted <> 1 " .
       "ORDER BY login_users.birthdate DESC " .
       "LIMIT 0, 20";

Answer 1

这是一种方法。查找随机照片，然后加入附加信息:

SELECT *
FROM (select lu.*,
             (select photo_id
              from profile_photos
              where lu.user_id = profile_photos.user_id
              order by RAND()
              limit 1
             ) as photo_id
      from login_users lu
     ) lu LEFT JOIN
     profile_photos pp
     ON pp.photo_id = lu.photo_id LEFT JOIN
     profiles p
     ON p.user_id = lu.user_id 
WHERE lu.restricted <> 1
ORDER BY lu.birthdate DESC
LIMIT 0, 20

这里假设 photo_id 是 profile_photos 的主键。