table1>画廊|每个都由不同的ID标识

table2>图片|画廊中的图像

table3> galImgLink |每条记录将图像链接到图库

select
  galId,
  galName
from
  galleries
limit
  0, 10


通过从链接表中选择随机记录,将图像连接到上面选择的每个记录

select
   lnkImgId
from
   galImgLink
where
    lnkGalId = galId
ORDER BY
  RAND()
LIMIT
  0, 1


然后从图像表中获取相关图像

select
  imgFile,
  imgTitle
from
  Images
where
  imgId = lnkImgId and
  imgStatus = 'active'


如何将它们组合成一个查询?

最佳答案

我相信类似以下的方法会起作用:

SELECT galleries.galId, galleries.galName, Images.imgFile
FROM galleries
JOIN Images
ON Images.imgId = (
    SELECT lnkImgId
    FROM galImgLink
    WHERE lnkGalId = galleries.galId
    ORDER BY RAND()
    LIMIT 1
)

10-06 15:14