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
)