使用join和limit的postgresql查询

Question

我有一份桌子礼物。Gift可以通过一个表关联拥有多个gift_images。我正在尝试返回至少有一个gift_images关联的具有特定隐私级别的有限#礼物。

本质上，我想返回: gift条目和它的第一个关联的gift_image (gift_image应该按照它拥有的位置值排序，位置1是第一个)。没有关联gift_image的礼物应该被忽略。

这就是我所拥有的，但它肯定不起作用。

SELECT gifts.* FROM gifts LEFT JOIN gift_images ON gifts.id = gift_images.gift_id WHERE gifts.privacy = 2 ORDER BY gift_images.position ASC LIMIT 10

有什么帮助吗？

Answer 1

如果你想忽略没有图片的礼物，你应该使用INNER JOIN而不是LEFT JOIN。此外，为了使查询有意义，除了从gift中选择字段之外，还应该从gift_images中选择一些字段。

如果所有具有礼物图像的礼物都有一个position = 1的图像，则此查询应执行以下操作：

SELECT gifts.*, gift_images.*
FROM gifts
INNER JOIN gift_images
ON gifts.id = gift_images.gift_id
WHERE gifts.privacy = 2
AND gift_images.position = 1
LIMIT 10

否则，您可以尝试

SELECT gifts.*, gift_images.*
FROM gifts
INNER JOIN (SELECT gift_id, MIN(position) AS min_position
            FROM gift_images
            GROUP BY gift_id) AS positions
ON positions.gift_id = gifts.id
INNER JOIN gift_images
ON gift_images.gift_id = gifts.id
AND gift_images.position = positions.min_position